SQL Server: Greatest-N-per-group extended

Question

My table looks like this:

ABCD 1 1 1 1 1 1 3 2 1 1 0 4 1 1 2 1 1 2 1 0 1 2 0 2 1 2 4 5 2 1 5 3 

My goal is for each pair of A and B to output a value in D corresponding to MIN (C) and a value in D corresponding to MAX (C). The exit should be

 ABD at MIN(C) D at MAX(C) 1 1 4 2 1 2 2 5 2 1 3 3 

I know that to extract MIN (C) and MAX (C) I just do:

 SELECT A, B, MIN(C) as "minC", MAX(C) as "maxC" FROM Table GROUP BY A, B ORDER BY A, B 

My question is: how do I get column D to travel? If I include it in the SELECT and GROUP BY clauses, it will generate MIN (C) and MAX (C) for each D, which I don't want. Moreover, I do not even need to output MIN (C) and MAX (C). D is all I need.

The basic schema presented in SQL. Select only the rows with the maximum value in the column do not seem to handle this case.

Thanks in advance!

Answer 1

Your query might look something like this:

 ;with C as ( select A, B, C, D, row_number() over(partition by A, B order by C asc) as rn1, row_number() over(partition by A, B order by C desc) as rn2 from YourTable ) select C1.A, C1.B, C1.D as "D at MIN(C)", C2.D as "D at MAX(C)" from C as C1 inner join C as C2 on C1.A = C2.A and C1.B = C2.B where C1.rn1 = 1 and C2.rn2 = 1 

The first part is a common table expression (CTE) that encapsulates a query that can be reused later in the main query. It uses row_number () to list the rows in each section. rn1 ordered on C asc , therefore rn1 = 1 for the minimum value of C and rn2 ordered on C desc , which means that rn2 = 1 for the maximum value of C The main query is that the CTE joins columns A and B twice. The where clause ensures that we only get strings where rn1 and rn2 are 1 .

Here is a working example that uses table @T instead of your table.

 declare @T table ( A int, B int, C int, D int ) insert into @T values (1, 1, 1, 1), (1, 1, 3, 2), (1, 1, 0, 4), (1, 1, 2, 1), (1, 2, 1, 0), (1, 2, 0, 2), (1, 2, 4, 5), (2, 1, 5, 3) ;with C as ( select A, B, C, D, row_number() over(partition by A, B order by C asc) as rn1, row_number() over(partition by A, B order by C desc) as rn2 from @T ) select C1.A, C1.B, C1.D as "D at MIN(C)", C2.D as "D at MAX(C)" from C as C1 inner join C as C2 on C1.A = C2.A and C1.B = C2.B where C1.rn1 = 1 and C2.rn2 = 1 

Answer 2

Something like this might work:

 SELECT A, B, MIN(C) as "minC", MAX(C) as "maxC", (SELECT TOP 1 D FROM Table [table2] WHERE table1.A = table2.A AND table1.B = table2.B AND MIN(table1.C) = table2.C) As [D] FROM Table [table1] GROUP BY A, B ORDER BY A, B