8

Recently i found that SQLite don't support DISTINCT ON() clause that seems postgresql-specific. For exeample, if i have table t with columns a and b. And i want to select all items with distinct b. Is the following query the only one and correct way to do so in SQLite?

select * from t where b in (select distinct b from t)

Sample data:

a | b
__|__
1   5
2   5
3   6
4   6

What i expect in return:

a | b
__|__
1   5
3   6
grigoryvp
  • 40,413
  • 64
  • 174
  • 277
  • 4
    That query will always return all rows from t, since every b in t is part of (select distinct b from t). Maybe you can clarify a bit more what you are trying to do, and provide some sample data and expected results? – dcp Jun 14 '10 at 15:46
  • 1
    Unless you explain why `(1,5)` is in your expected result set while `(2,5)` isn't (and why it is exactly this way, and not the other way around), this will be hard to answer. – Tomalak Jun 14 '10 at 15:58
  • Any constrait will do. Minimum, maximum etc. Assumed that real query will need additional columns on DISTINCT only if such data has meaning - for example, it's additional columns are same for same DISTINCT columns. – grigoryvp Jun 14 '10 at 16:03

2 Answers2

12
sqlite> SELECT * FROM t GROUP BY b;
2|5
4|6
(for each b: one (unpredictable) value of a)

sqlite> SELECT * FROM (SELECT * FROM t ORDER BY a DESC) GROUP BY b;
1|5
3|6
(for each b: the row with min a)

sqlite> SELECT * FROM (SELECT * FROM t ORDER BY a ASC) GROUP BY b;
2|5
4|6
(for each b: the row with max a)
Solimo
  • 121
  • 1
  • 2
11

Use:

  SELECT MIN(t.a) AS A,
         t.b
    FROM TABLE t
GROUP BY t.b
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502