CODEWITHSUNDEEP
sqlsql-server
cod1358
cod1358

Reputation: 23

SQL - Results based partially on aggregate of particular column

Thanks in advance for any assistance. I have a situation where I need a snapshot of SQL data but part of the results need to be based on the aggregate of one column. Here's a tiny subset of my data:

| A | B | last_date  | next_date  | C | D |
| 1 | 3 | 01/01/2000 | 01/01/2003 | 1 | 1 |
| 1 | 3 | 01/01/2001 | 01/01/2004 | 1 | 2 |
| 2 | 3 | 01/01/2002 | 01/01/2005 | 2 | 3 |
| 2 | 4 | 01/01/2003 | 01/01/2006 | 3 | 4 |

My results need to be grouped by columns A and B, the MAX of last_date and the MIN of next date. But the kicker is that the values for columns C and D should be the values that correspond to the MIN of next date. So for the above data subset my results would be:

| A | B | last_date  | next_date  | C | D |
| 1 | 3 | 01/01/2001 | 01/01/2003 | 1 | 1 |
| 2 | 3 | 01/01/2002 | 01/01/2005 | 2 | 3 |
| 2 | 4 | 01/01/2003 | 01/01/2006 | 3 | 4 |

Note how the first row of results has the value of last_date from the 2nd row of the initial data, but the values for columns C and D correspond to the first row from the initial data. In the case where there is an exact duplication of columns A, B, max(last_date), and min(next_date) but the values for columns C and D don't match, then I don't care which one is returned - but I must only return one row, not multiples.

Upvotes: 2

Views: 60

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use row_number adn get this results as below:

Select A, B, MaxLast_date, MinNext_date, C, D from (
    select *, max(last_date) over(partition by A, B) as MaxLast_date, Min(next_date) over(partition by A, B) as MinNext_date,
        next_rn = Row_number() over(partition by A, B order by next_date)   from #yourtable
) a 
Where a.next_rn = 1

Other way is with top (1) with ties as below:

Select top(1) with ties *, max(last_date) over(partition by A, B) as MaxLast_date, Min(next_date) over(partition by A, B) as MinNext_date
    from #yourtable
    Order by Row_number() over(partition by A, B order by next_date)

Output:

+---+---+--------------+--------------+---+---+
| A | B | MaxLast_date | MinNext_date | C | D |
+---+---+--------------+--------------+---+---+
| 1 | 3 | 2001-01-01   | 2003-01-01   | 1 | 1 |
| 2 | 3 | 2002-01-01   | 2005-01-01   | 2 | 3 |
| 2 | 4 | 2003-01-01   | 2006-01-01   | 3 | 4 |
+---+---+--------------+--------------+---+---+

Demo

Upvotes: 1

Related Questions