CODEWITHSUNDEEP
sqlsql-server
HelloMasters
HelloMasters

Reputation: 13

Get Top N row from each set from table with 4 column in SQL Server

Assume I have a table with 4 columns: 

Col1   Col2   Col3   Col4

My initial query is : 

SELECT Col1, Col2, Col3, Col4  
FROM myTable  
ORDER BY Col1, Col2, Col3 DESC, Col4

My desired result is all 4 columns, but with this condition that Top N Col3 different row when Col1, Col2 is equal.

Example with N=2 :

Table sample data:

Col1 Col2 Col3  Col4  
---------------------
1    a    2000  s  
1    a    2002  c  
1    a    2001  b  
2    b    1998  s  
2    b    2002  c  
2    b    2000  b  
3    c    2000  b  
1    f    1998  n  
1    g    1999  e

Desired result:

1    a    2002  c  
1    a    2001  b  
1    f    1998  n  
1    g    1999  e  
2    b    2002  c  
2    b    2000  b  
3    c    2000  b

In another description, when (col1, col2) is repeated in multiple records, just export top N rows of those records when order by Col3 descending.

Can I do this with SQL script, without hard coding?

Upvotes: 1

Views: 120

Answers (3)

Faraz PV
Faraz PV

Reputation: 522

METHOD 1- FOR MSSQL

http://sqlfiddle.com/#!6/4bda39/6

with a as (
    select ROW_NUMBER() over(partition by t.col1,t.col2 order by t.col3 desc) as row,t.* 
    from myTable as t) 
select * from a where a.row <= 2

Replace a.row <= 2 (2 with your N)

METHOD 2- FOR MYSQL

http://sqlfiddle.com/#!9/79e81a/63

SELECT   myTable.Col1, myTable.Col2, myTable.Col3, myTable.Col4
FROM ( 
    Select Col1 as Col1, Col2 as Col2, count(Col1) as cc, AVG(Col3) as aa 
    From myTable 
    group by Col1, Col2) as tt
join  myTable on myTable.Col1 = tt.Col1 and myTable.Col2 = tt.Col2
where myTable.Col3 >= tt.aa
Order by Col1 ,Col2 ,Col3 Desc,Col4

METHOD 3- FOR MYSQL

http://sqlfiddle.com/#!9/79e81a/79

SELECT * FROM (
    SELECT CASE Col1
        WHEN @Col1 THEN
            CASE Col2 
                WHEN @Col2 THEN @curRow := @curRow + 1 
                ELSE @curRow := 1
            END
        ELSE @curRow :=1
    END AS rank,
    @Col1 := Col1 AS Col1,
    @Col2 := Col2 AS Col2, 
    Col3, Col4
    FROM myTable p
    JOIN (SELECT @curRow := 0, @Col1 := 0, @Col2 := '') r
    ORDER BY Col1, Col2, Col3 DESC) as tt 
WHERE tt.rank <= 2

Replace tt.rank <= 2 replace 2 by your desired index

Upvotes: 0

Ajay
Ajay

Reputation: 774

I think below code was as expected 

declare @tab table (Col1 int, Col2 char(1), Col3 int, Col4 char(1))
declare @N int
insert into @tab
select 1,    'a'   , 2000,  's' 
union all
select 1 ,   'a'  ,  2002 , 'c'  
union all
select 1 ,   'a'  ,  2001 , 'b'  
union all
select 2 ,   'b'  ,  1998 , 's'  
union all
select 2 ,   'b'  ,  2002  ,'c'  
union all
select 2 ,   'b'  ,  2000  ,'b'  
union all
select 3 ,   'c'  ,  2000  ,'b'  
union all
select 1 ,   'f'  ,  1998  ,'n'  
union all
select 1 ,   'g'  ,  1999  ,'e'

;with tab as
(
select ROW_NUMBER() over(partition by t.col1,t.col2 order by t.col3 desc) as row,t.* 
from @tab t
)

select Col1,Col2,Col3,Col4 
from tab
where row < 3

output

Col1    Col2    Col3    Col4
1        a      2002    c
1        a      2001    b
1        f      1998    n
1        g      1999    e
2        b      2002    c
2        b      2000    b
3        c      2000    b

Upvotes: 0

sepupic
sepupic

Reputation: 8687

declare @t table (Col1 int, Col2 char, Col3 int,  Col4 char);

insert into @t values  
(1,    'a',    2000,  's'),  
(1,    'a',    2002,  'c'),  
(1,    'a',    2001,  'b'),  
(2,    'b',    1998,  's'),  
(2,    'b',    2002,  'c'),  
(2,    'b',    2000,  'b'),  
(3,    'c',    2000,  'b'),  
(1,    'f',    1998,  'n'),  
(1,    'g',    1999,  'e');


declare @N int = 2; -- number per "top"

with cte as
(
select *,
       row_number() over(partition by col1, col2 order by col3 desc) as rn
from @t
)

select *
from cte c
where rn <= @N;

Upvotes: 1

Related Questions