0

Can you please tell how to select the highest record in a set of records. For example in the below table I want to get only the record which has the latest date (in bold).

TYP SEQ DATE
A 1 1900-01-01
A 1 2002-01-06
A 1 2021-02-01
A 2 1900-01-01
A 2 2003-12-01
A 2 2004-12-05
A 2 2021-02-21
A 2 2021-01-06
A 3 2015-05-17
A 3 2017-11-26
A 3 2019-11-10
A 3 2021-01-01

Thank you very much!

Sandeepreddy Q
  • 9
  • 2
  • Please share table names and queries you have already tried. What is the database you're using? – fancyuserid Feb 15 '21 at 05:16
  • 1
    Haven't you tried anything yourself? Where are you stuck? What is your DBMS? You should always tag your SQL questions with the DBMS you are using. – Thorsten Kettner Feb 15 '21 at 07:33

2 Answers2

0

First group by the table with SEQ, then join the table to the same table and create the rows with ROW_NUMBER()and sort by date, and then show the results in the output with records whose row number is one.

WITH resultTable AS (
SELECT 
RT.TYP,
RT.SEQ,
RT.DATE,   
ROW_NUMBER() OVER ( PARTITION BY RT.SEQ 
                         ORDER BY RT.DATE DESC ) AS [ROW NUMBER]
FROM MyTable RT
Inner JOIN 
(
  SELECT SEQ
  FROM MyTable
 GROUP BY SEQ
) LT ON RT.SEQ = LT.SEQ)
select resultTable.TYP,resultTable.SEQ,resultTable.DATE from resultTable
WHERE resultTable.[ROW NUMBER] = 1
Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
0

A simple window function should do it:

select typ, seq, date
from (
  select typ, seq, date, 
         dense_rank() over (partition by typ, seq order by date desc) as rn
  from the_table
) t
where rn = 1
order by typ, seq;