Extract all the records apart from the least date (Min Date)

Question

How can we get all the records apart from the least date (Min Date)

Assume this is my Input

ID DATE
1 23/03/1990
1 24/04/2016
1 25/05/2016
1 23/03/1990
1 25/05/2018

Expected Output

ID DATE
1 24/04/2016
1 25/05/2016
1 25/05/2018

Can we perform this operation in a single line query?

score 1 · Answer 1 · edited May 23 '17 at 11:48

1

Other ways outlined here

    SELECT ID, 
           DATE
    FROM (SELECT distinct 
                 ID,
                 DATE,
                 row_number() over (order by DATE) rnk
          FROM table_name)
    WHERE rnk >1;

edited May 23 '17 at 11:48

Community

1
1

answered Oct 28 '16 at 05:07

M O'Connell

487
5
18

score 0 · Answer 2 · answered Oct 28 '16 at 05:09

Join to a query that returns the min:

select t.id, date
from mytable t
left join (select id, min(date) min
           from mytable
           group by id) x
  on t.id = x.id and date = min
where x.id is null

It works by returning only missed joins.

This query will work on (virtually) any database.

score 0 · Answer 3 · answered Oct 28 '16 at 05:29

0

Try this

select * from tableA where datecolmn not in (select min(datecolmn) as dt from tableA a group by ID)

answered Oct 28 '16 at 05:29

Atul

440
8
24

Extract all the records apart from the least date (Min Date)

3 Answers3