How to get last entry of group?

user_16511234 0
Viewed 32

I have a table (which has columns form other tables) with timestamp, value and two idenitification columns with names.

My goal is to get the last entry for every group.

When I do a query with this:

SELECT MAX(time) AS time, 
       id,
       name,
       value
FROM table
WHERE time >= (now() - '00:05:00)
GROUP BY id, name, value

It still shows me double values for name from previous timestamps. When I leave value out all is fine and it shows me each id and name. How can I get to show me the value without the double entry?

My goal would be something like this (short version):

time,value,"id","name"
2022-08-31 11:58:00+02,0,"A","D11"
2022-08-31 11:58:00+02,0,"A","D12"
2022-08-31 11:58:00+02,0,"A","D13"

The table looks like this:

time,value,"id","name"
2022-08-31 11:58:00+02,0,"A","D11"
2022-08-31 11:57:00+02,1,"A","D11"
2022-08-31 11:56:00+02,2,"A","D11"
2022-08-31 11:59:00+02,3,"A","D11"
2022-08-31 11:58:00+02,1,"A","D12"
2022-08-31 11:56:00+02,3,"A","D12"
2022-08-31 11:59:00+02,4,"A","D12"
2022-08-31 11:59:00+02,0,"A","D20"
2022-08-31 11:56:00+02,1,"A","D21"
2022-08-31 11:58:00+02,7,"A","D21"
2022-08-31 11:59:00+02,8,"A","D21"
2022-08-31 11:57:00+02,10,"A","D21"
2022-08-31 11:56:00+02,1,"A","D22"
2022-08-31 11:59:00+02,0,"A","D34"
2022-08-31 11:57:00+02,2,"A","D41"
2022-08-31 11:59:00+02,3,"A","D41"
2022-08-31 11:56:00+02,4,"A","D41"
2022-08-31 11:58:00+02,5,"A","D41"
2022-08-31 11:59:00+02,0,"A","D42"
2022-08-31 11:56:00+02,1,"A","D42"
2022-08-31 11:57:00+02,5,"A","D42"
2022-08-31 11:59:00+02,0,"A","D43"
2022-08-31 11:58:00+02,3,"A","D43"
2022-08-31 11:56:00+02,4,"A","D43"
2022-08-31 11:57:00+02,7,"A","D43"
2022-08-31 11:59:00+02,0,"A","D53"
2022-08-31 11:56:00+02,1,"A","D53"
2022-08-31 11:59:00+02,0,"A","D57"
2022-08-31 11:57:00+02,2,"B","D11"
2022-08-31 11:59:00+02,3,"B","D11"
2022-08-31 11:59:00+02,1,"B","D12"
2022-08-31 11:58:00+02,3,"B","D12"
2022-08-31 11:58:00+02,1,"B","D13"
2022-08-31 11:59:00+02,2,"B","D13"
2022-08-31 11:57:00+02,4,"B","D13"
2022-08-31 11:58:00+02,1,"B","D31"
2022-08-31 11:57:00+02,7,"B","D31"
2022-08-31 11:59:00+02,11,"B","D31"
2022-08-31 11:57:00+02,1,"B","D32"
2022-08-31 11:58:00+02,3,"B","D32"
2022-08-31 11:59:00+02,4,"B","D41"
2022-08-31 11:58:00+02,9,"B","D41"
2022-08-31 11:57:00+02,10,"B","D41"
2022-08-31 11:56:00+02,2,"B","D42"
2022-08-31 11:59:00+02,4,"B","D42"
2022-08-31 11:58:00+02,7,"B","D42"
2022-08-31 11:57:00+02,9,"B","D42"
2022-08-31 11:58:00+02,1,"B","D43"
2022-08-31 11:57:00+02,2,"B","D43"
2022-08-31 11:59:00+02,3,"B","D43"
2022-08-31 11:56:00+02,0,"C","D11"
2022-08-31 11:59:00+02,2,"C","D11"
2022-08-31 11:57:00+02,0,"C","D12"
2022-08-31 11:56:00+02,1,"C","D12"
2022-08-31 11:59:00+02,2,"C","D12"
2022-08-31 11:58:00+02,3,"C","D12"
2022-08-31 11:59:00+02,0,"C","D13"
2022-08-31 11:58:00+02,1,"C","D13"
2022-08-31 11:56:00+02,1,"C","D21"
2022-08-31 11:59:00+02,2,"C","D21"
2022-08-31 11:58:00+02,4,"C","D21"
2022-08-31 11:59:00+02,3,"C","D22"
2022-08-31 11:58:00+02,5,"C","D22"
2022-08-31 11:56:00+02,8,"C","D22"
2022-08-31 11:59:00+02,1,"C","D23"
datetime postgresql
1 Answers

You can use the rank window function to well, rank, rows per some grouping, and then take the first one:

SELECT time, id, name, value
FROM   (SELECT time, id, name, value, 
               RANK() OVER (PARTITION BY id, name, value ORDER BY time DESC) AS rk
        FROM   mytable
        WHERE  time >= (now() - '00:05:00') t
WHERE  rk = 1
user_24227760
Related