Sqllite query on distinct or certain value

Question

I have an android sqllite database. It has a text column called chainid.

I'd like to return all columns from rows with DISTINCT chainids || or where chainid is equal to: "none".

So e.g.:

| ID| Name | chainid |
| 1 | widgetname1 | 12345 |
| 2 | widgetname2 | 12345 |
| 3 | widgetname3 | "none" |
| 4 | widgetname4 | 49390 |
| 5 | widgetname5 | 49390 |

Given the above table I would like my query to return 3 rows with all columns for row 2, row3 and row5. -- So DISTINCT on chainid OR where chainid = "none" with the max id selected as the distinct row

Can I achieve this in one query?

I could return all and then process afterwards in java, but this is inefficient.

j.holetzeck j.holetzeck · Accepted Answer · 2013-03-23T11:39:48

What about

select *
from table where id in
( select max(id)
  from table
  group by chainid
  where chainid != 'none'
  union
  select id
  from table
  where chainid = 'none'
)

Sqllite query on distinct or certain value

1 Answers