Highest value for each GROUP

Question

I have a table with 2 columns (there are more but these are the important ones) timestamp and analysisId. There is no constraint on either but in practice timestamp will be unique. Many rows have the same analysisId and different timestamps. I need a query that returns only the highest timestamp for each analysisId

So for example the data may look something like

timestamp | analysisId
1234      | 1
1236      | 1
1300      | 2
1337      | 3
1400      | 3

And the result I would want would be

timestamp | analysisId
1236      | 1
1300      | 2
1400      | 3

Currently, I have

SELECT "timestamp", analysisId FROM myData GROUP BY (analysisId, "timestamp") ORDER BY "timestamp" DESC LIMIT 1;

However of course this only gives me one result, whereas I want each result per analysisId

If you want one row per analysisId, simply `GROUP BY analysisId`. — jarlh, Nov 04 '22 at 11:17
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — YUNG FOOK YONG, Nov 04 '22 at 11:17
Why `LIMIT 1`? Skip LIMIT as you want several rows returned. — jarlh, Nov 04 '22 at 11:21

score 0 · Accepted Answer · answered Nov 04 '22 at 11:16

0

This is a simple aggregate using max

select analysisId, max(Timestamp) as Timestamp
from t
group by AnalysisId;

answered Nov 04 '22 at 11:16

Stu

30,392
6
14
33

Highest value for each GROUP

1 Answers1