Why this patrially non-aggregated group-by works?

Question

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...

It would be useful to see what the data you're querying looks like — OneCricketeer, Apr 23 '16 at 17:20

score 1 · Answer 1 · edited Apr 25 '16 at 11:09

1

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)

edited Apr 25 '16 at 11:09

marc_s

732,580
175
1,330
1,459

answered Apr 23 '16 at 17:41

Hogan

69,564
10
76
117

score 1 · Accepted Answer · answered Apr 23 '16 at 17:45

1

This is guaranteed to work since SQLite 3.7.11.

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

answered Apr 23 '16 at 17:45

CL.

173,858
17
217
259

So according to [this](http://stackoverflow.com/a/4377116/3766649), only supported on android versions 4.1+. Thanks. – jazzgil Apr 23 '16 at 21:21

Why this patrially non-aggregated group-by works?

2 Answers2