CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Louis Ye
Louis Ye

Reputation: 154

How to max one column than max another column to avoid repeated combinations SQL

a b c
ID1 Date1 5
ID1 Date2 5
ID2 Date3 4
ID2 Date4 4
ID3 Date5 5
ID4 Date6 6
ID5 Date7 2
ID6 Date8 2

a is an ID (String) b is a date c is a string d is an int

This is the SQL query I am using

select S."a","b","c" from
    (select max("d"), "a"
    from public."x"
    group by "a") F
    join "x" S on F."max" = S."d"
    and S."a" = F."a"
    LIMIT 10;

How do I only keep unique pairings of a and c in my table. Such that the row that is kept is the pairing with the later b value (later date)? For example, if Date1 in the table was later than date2 in the table, I would only want to include the ID1/C combo containing Date1 rather than also including the ID1/C combo containing Date2.

I'm new to Sql thank you for your patience!

Upvotes: 0

Views: 77

Answers (1)

LoztInSpace
LoztInSpace

Reputation: 5697

select a,c,max(b) from 
(<your query>) as Q
group by a,c

Assuming above has a limit of 10 to keep it simple. You'll just get grouping on some random 10 if you keep it there with no order by.

If you really want 10 you need to move the TOP outside the inner query and add an ORDER BY so it picks a useful 10.

Upvotes: 1

Related Questions