CODEWITHSUNDEEP
sqlpostgresqltimestampgreatest-n-per-group
RobbeM
RobbeM

Reputation: 757

pgsql: Select different unique columns with newest timestamp

I have a psql table with columns: write_date, tagwaarde, tagnaam, tagkwaliteit, schrijven. What I need to do now is to retrieve only the newest unique value for each column where "schrijven" is true.

Hard to explain so here is an example: enter image description here

In this case I want this output:

[('Bakkerij.Device1.DB100INT0', 777), ('Bakkerij.Device1.DB100INT8', 0)]

Because:

Bakkerij.Device1.DB100INT0 = 777 and Bakkerij.Device1.DB100INT0 = 666

=> 777 has the newest timestamp

I have tried SELECT tagnaam, tagwaarde FROM inlezen WHERE schrijven = True ORDER BY write_date DESC LIMIT 1

This gives just 

[('Bakkerij.Device1.DB100INT8', 0)]

as output.

I have tried some things with DISTINCT but I couldn't get it to work.

What would be the right way to solve this? Thanks in advance!

Upvotes: 0

Views: 73

Answers (2)

joop
joop

Reputation: 4523

Finding the "most recent one" can always be expressed in terms of there does not exist a more recent one :

SELECT tagnaam, tagwaarde 
FROM inlezen lz
WHERE lz.schrijven = True
AND NOT EXISTS (
        SELECT * FROM inlezen nx
        WHERE nx.tagnaam = lz.tagnaam
        AND nx.schrijven = True
        AND nx.write_date > lz.write_date
        );

Upvotes: 0

user330315
user330315

Reputation:

This can be done with Postgres' distinct on () operator:

SELECT distinct on (tagnaam) tagnaam, tagwaarde 
FROM inlezen 
WHERE schrijven = True 
ORDER BY tagnaam, write_date DESC

If you prefer a standard ANSI SQL solution, then this can be done using a window function:

select tagnaam, tagwaarde
from (
    SELECT tagnaam, tagwaarde, 
           row_number() over (partition by tagnaam order by write_date DESC) as rn
    FROM inlezen 
    WHERE schrijven = True 
) t
where rn = 1
order by tagnaam;

The distinct on is usually faster than the solution with the window function.

Upvotes: 2

Related Questions