How to select Max of each row?

Question

I am using Postgres SQL and I would like to return the rows which has the greatest seq number.

Eg:

row_name, seq, data
row1, 1, abc
row1, 2, def
row2, 1, hij
row2, 2, klm
row2, 3, nop

I want the result to be :

row1, 2, def
row2, 3, nop

I am using:

SELECT * FROM table_name where seq = (SELECT MAX(seq) from table_name)

but it is returning only row2, 3, nop.

Please help thanks.

Answer 1

Use a subquery then inner join the max(seq).

select * from table_name t
inner join 
    (select max(seq) seq, row_name from table_name group by row_name) t1 on t1.row_name = t.row_name and t.seq = t1.seq

Answer 2

You can also use ROW_NUMBER for getting your desired output as below-

DEMO HERE

SELECT row_name, seq, data 
FROM 
(
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY row_name ORDER BY seq DESC) RN
    FROM your_table
)A
WHERE RN = 1

Answer 3

SELECT 
    row_name, seq, data
FROM (
  SELECT 
    row_name, seq,  data, 
    max( seq) over(partition by row_name) max_seq 
  FROM your_table
) S
where seq=max_seq

Answer 4

Typically the most efficient solution in Postgres for this type of problems is to use distinct on ()

select distinct on (row_name) row_name, seq, data
from the_table
order by row_name, seq desc;

Answer 5

we can use do it by descending order by too.

 SELECT *FROM table_name ORDER BY column1, column2 DESC