CODEWITHSUNDEEP
sql-server-2008greatest-n-per-group
ddp26
ddp26

Reputation: 43

SQL Server: Greatest-N-per-group expanded

My table looks like:

A     B     C     D
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 to, for each pair of A and B, output the value in D that corresponds to MIN(C), and the value in D that corresponds to MAX(C). The output should be

A    B    D 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 simply 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 bring column D along for the ride? If I include it in the SELECT and GROUP BY clauses, it will generate the MIN(C) and MAX(C) for each D, which is not what I want. Moreover, I don't even need to output MIN(C) and MAX(C). D is all that I'm after.

The basic outline provided in SQL Select only rows with Max Value on a Column does not seem to handle this case.

Thanks in advance!

Upvotes: 4

Views: 520

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Your query could 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 enumerate the rows within each partition. rn1 is ordered by C asc so rn1 = 1 for the min value of C and rn2 is ordered by C desc and that means that rn2 = 1 for the max value of C. The main query is using the CTE two times join on A and B columns. The where clause makes sure that we only get the rows where rn1 and rn2 is 1.

Here is a working example that uses a table variable @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

Upvotes: 4

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Reputation: 5759

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

Upvotes: 0

Related Questions