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. There are many rows with the same analysisId and different timestamps. I need a query which 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
>Solution :
This is a simple aggregate using max
select analysisId, max(Timestamp) as Timestamp
from t
group by AnalysisId;