Get top n row of each group of two columns

Question

This question is different from Get top 1 row of each group. In my question, each group is consists of two columns (col1, col2), while in his question each group is consists of only one column (col1). I also tried to modify the answer in his question but failed.

Example:

Suppose n = 1

Input:

    col1  col2 x  Amt
    A     B    x1 100
    A     B    x2 200
    C     D    x3 400
    C     D    x4 500
    ...more data ...

Output:

    col1  col2 x  Amt
    A     B    x2 200
    C     D    x4 500
    ...more data ...

What I tried ...select *, row_numne() over ( partition by (col1, col2) order by ...

score 1 · Answer 1 · answered Dec 16 '16 at 20:56

1

why not simple max works for you?

select col1, col2, max(x), Max(Amt) from yourtable
  group by col1, col2

answered Dec 16 '16 at 20:56

Kannan Kandasamy

13,405
3
25
38

1

MAX() works for the sample data, but is there any guarantee that all data (as opposed to grouping) columns in the top row will always have the largest value for any possible grouping set? For example, if your query was looking for the row with the most items on it in each invoice, an invoice with 1 row with 100 $1.00 items and 1 row with 1 $100 item would report a row with 100 $100 items if MAX() is used. Cross-correlation is needed instead. – Laughing Vergil Dec 16 '16 at 21:00

score 1 · Accepted Answer · answered Dec 16 '16 at 21:01

You can still use row_number within a CTE. The idea is to get all the rows, per your grouping, that is <= the number you pass in. This is similar to getting the top n rows for your pairing based on the order of AMT

declare @count int = 1
with cte as(
    select 
        col1,
        col2,
        x,
        Amt,
        row_number() over (partition by col1, col2 order by Amt desc) as rn
    from yourTable)

select 
    col1,
    col2,
    x,
    Amt
from cte
where rn <= @count

John Cappelletti · Answer 3 · 2016-12-16T21:12:46.197

1

Declare @Top int = 1

Select col1,col2,x,Amt 
 From  (
         Select *
               ,RN=Row_Number() over (Partition By Col1,Col2 Order By Amt Desc) 
         From  YourTable ) A
 Where RN<=@Top

Returns

col1    col2    x   Amt
A       B       x2  200
C       D       x4  500

edited Dec 16 '16 at 21:12

answered Dec 16 '16 at 21:05

John Cappelletti

79,615
7
44
66

Probably faster than the CTE. Better idea – S3S Dec 17 '16 at 01:43
@scsimon can't imagine much difference either way ... if any – John Cappelletti Dec 17 '16 at 02:21
@scsimon Kind of a dull day for questions. I only had one that caught my attention http://stackoverflow.com/questions/41190208/measure-application-performance-by-clustering-sql-audit-records/41192265#41192265 – John Cappelletti Dec 17 '16 at 02:24

score 0 · Answer 4 · answered Dec 16 '16 at 21:16

And here is the CROSS APPLY option, with test tables to confirm its functionality:

DECLARE @MyTable TABLE (Col1 varchar(4) not null, Col2 varchar(4) not null, x varchar(8) not null, amt int not null)

INSERT INTO @myTable VAlues ('A', 'B', 'x1', 100)
INSERT INTO @myTable VAlues ('A', 'B', 'x2', 200)
INSERT INTO @myTable VAlues ('C', 'D', 'x4', 400)
INSERT INTO @myTable VAlues ('C', 'D', 'x3', 500)


DECLARE @n int
SET @n = 1

SELECT DISTINCT 
    m.Col1,
    m.Col2,
    m2.x,
    m2.Amt
FROM @MyTable m
CROSS APPLY (
    SELECT TOP(@n) Amt, x
    FROM @MyTable 
    WHERE col1 = m.Col1
        AND col2 = m.col2
    ORDER BY Amt Desc, x Desc
    ) m2

Get top n row of each group of two columns

4 Answers4