CODEWITHSUNDEEP
sql-servergroup-by
user3221917
user3221917

Reputation: 127

SQL Server Select the most recent record only

I have a table tat looks like this:

Table1

ID Date       TermDate Cancel
1  20140101   99999999 20140102
1  20140101   20130102 20140102

2  20140108   20130102 20140102
2  20140101   99999999 20140102

3  20140101   99999999 20140102
3  20140101   99999999 20140102
3  20140101   99999999 20140709

What I want to do is, to group by each ID and select only the most recent record. First I need to check the Date column, from there if a record is more recent then I do not need to check the other columns, in this case, it is ID #2.

If the Date column is the same for same ID, then I want to compare the Cancel column, whichever is the most recent then that record should be shown only, in this case it is ID 3.

If both Date and Cancel columns are the same, then I need to compare the TermDate column and the most recent should be displayed, in this case it is ID #1. All columns are int type.

Basically the result should be:

ID Date       TermDate Cancel
1  20140101   99999999 20140102
2  20140108   20130102 20140102
3  20140101   99999999 20140709

I am not the best person with self join tables and really not sure how to get started with this... what I currently do is: I copy the table into Excel file and then do the job manually... I am thinking to use CASE Statement but really do not know how to convert my logic to SQL proper code, here is my pseducode:

SELECT (CASE WHEN Date > DATE THEN SELECT the greater Date 
        CASE WHEN Date = Date THEN SELECT the grater Cancel
        ELSE WHEN Date = Date AND Cancel = Cancel THEN SELECT the grater TermDate END)
FROM Table1
GROUP BY ID

I don't even know how to make the table look like the above with case statement.

Please let me know what is the best way to do this... I searched online with no help.

Upvotes: 1

Views: 3566

Answers (1)

Szymon
Szymon

Reputation: 43023

You can use dense_rank function to assign a rank to each row within one ID based on your ordering and get the first one for each ID:

select id, [date], termdate, cancel from
    (select id, [date], termdate, cancel,
        dense_rank() over (partition by id order by [date] desc, cancel desc, termdate desc) rank
    from table1) X
where rank = 1

SQL Fiddle Demo

Upvotes: 3

Related Questions