SQLTeam.com | Weblogs | Forums

Extract record based on Condition between columns

Hi , I need to extract records based on few conditions.
Based on ID column , we have to extract records from other columns.
Case1: If ID column is same, then we have to check for Date column and pick the minimum date.
Case2: If ID column is same and Date also same then we have to check Salary column and pick the highest salary.
Case3: If ID column is same , date and Salary is same we can pick one record. There are many other columns as well which may have different values.

Eg:
|ID|DATE|SALARY|DEPT|

|1|01-Jan|2000|10|
|1|02-Jan|3000|20|
|2|01-Jan|2000|10|
|2|01-Jan|3000|20|
|2|01-Jan|4000|20|
|3|01-Jan|1000|10|
|4|01-Jan|1000|10|
|4|02-Jan|2000|20|
|4|03-Jan|3000|30|
|5|04-Jan|1000|10|
|5|04-Jan|1000|20|

image

If you see the ID =1, need to pick 1st Jan record as both dates are different in both records.
if you see ID=2, Dates are same , so we need to move to next column which is salary and pick the highest one which is 4000.
If you see ID=3 , it has only 1 record, so we have to extract only 1.
If you see ID=4,As all dates are different , we can pick the minimum date record.
if you see ID=5, both date and salary are same, we can pick any one.

Output:

ID DATE SALARY DEPT
1 01-Jan 2000 10
2 01-Jan 4000 20
3 01-Jan 1000 10
4 01-Jan 1000 10
5 04-Jan 1000 10

SELECT ID, Date, Salary /*, ...*/
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date, Salary DESC) AS row_num
    FROM dbo.table_name
) AS derived
WHERE row_num = 1