CODEWITHSUNDEEP
sqlsqlite
jazzgil
jazzgil

Reputation: 2366

Why this patrially non-aggregated group-by works?

Any idea why the following two queries produce the same results? According to all references I've seen, the first shouldn't even work, but it does. Obviously, if its not a glitch, I prefer the first as it's a simpler and probably faster query.

SELECT *,MIN(ts) FROM tbl GROUP BY id;

SELECT *,ts FROM tbl a WHERE rowid IN 
     (SELECT rowid FROM tbl b WHERE a.id = b.id ORDER BY ts LIMIT 1);

Here's some sample data:

rowid   id  ts  name
    1   58  10  aaa
    2   58   5  bbb
    3   72   7  ccc

result expected:

    2   58   5  bbb  5     
    3   72   7  ccc  7

SQLFiddle here...

Upvotes: 0

Views: 42

Answers (2)

Hogan
Hogan

Reputation: 70531

This works because some platforms that don't support windowing functions treat this construct as a windowing function -- on SQL Server, Oracle and db2 you would have to write

 min(ts) over(order by ts)

Upvotes: 1

CL.
CL.

Reputation: 180141

This is guaranteed to work since SQLite 3.7.11.

As for the why: some paying customer wanted it ...

Upvotes: 1

Related Questions