SQL GROUP_BY choose from non-grouped column

Question

I have SQL Table with following columns:

| Key | Created_at | Acc_id |

SQL is running in ONLY_FULL_GROUP_BY mode.

Im' trying to get, for each Acc_id, the Key that has smallest Created_at. Naturally, I would write something like

SELECT Key
       , MIN(Created_at)
       , Acc_id 
From MyTable 
GROUP BY Acc_id

But query fails, because I have ONLY_FULL_GROUP_BY and it does not know which Key to choose from the groups. I want the one corresponding to MIN(Created_at) (the one from same row), but how do I tell it?

Thanks.

Possible duplicate of https://stackoverflow.com/questions/1313120/ — Bill Karwin, Nov 19 '19 at 17:15
@ZhaniBaramidze please show some sample data and desired result data . Thanks! — VBoka, Nov 19 '19 at 17:39

Gordon Linoff · Answer 1 · 2019-11-19T19:59:26.793

0

Don't use group by. Use filtering. here is an example:

select t.*
from mytable t
where t.created_at = (select min(t2.created_at)
                      from mytable t2
                      where t2.acc_id = t.acc_id
                     );

This query will have optimal performance with an index on mytable(acc_id, created_at).

edited Nov 19 '19 at 19:59

answered Nov 19 '19 at 16:58

Gordon Linoff

1,242,037
58
646
786

in t I'm only searching with created_at? what if several rows of different acc have same? – Zhani Baramidze Nov 19 '19 at 17:10
@ZhaniBaramidze . . . I'm not sure what your concern is. This is a *correlated* subquery, so it only returns the rows with the max `created_at` for each `acc_id`. – Gordon Linoff Nov 19 '19 at 17:46
query never ends :D which indexes do you think are needed? – Zhani Baramidze Nov 19 '19 at 17:56
I think the query is OK but change the MAX for MIN to get the result you want. – regisls Nov 19 '19 at 18:37

forpas · Answer 2 · 2019-11-19T17:33:50.357

0

With NOT EXISTS:

SELECT t.*
FROM MyTable t
WHERE NOT EXISTS (
  SELECT 1 FROM MyTable m
  WHERE m.Acc_id = t.Acc_id AND m.Created_at < t.Created_at
)

edited Nov 19 '19 at 17:33

answered Nov 19 '19 at 17:06

forpas

160,666
10
38
76

SQL GROUP_BY choose from non-grouped column

2 Answers2