CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bysql-server-2014
Haminteu
Haminteu

Reputation: 1334

SELECT TOP 2 MAX Value Group By SQL

I have the following table:

-----------------------------------------
xDate        xItem       xCount
-----------------------------------------
2018-01-01   A           100
2018-01-01   B           200
2018-01-01   D           500
2018-01-02   C           200
2018-01-02   E           800

I want to select TOP 2 value for each date on the MAX value of xCount field. So, the result should be:

-----------------------------------------
xDate        xItem      xCount
-----------------------------------------
2018-01-01   D          500
2018-01-01   B          200
2018-01-02   E          800
2018-01-02   C          200

Does anyone have an idea for this case?
Cheers,

Upvotes: 5

Views: 3826

Answers (5)

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

You can use ROW_NUMBER() and partition it on the basis of xDate and order by xCount to get what you want.

select X.xDate, 
       X.xItem, 
       X.xCount
  from 
       (select xDate, 
               xItem, 
               xCount, 
               row_number() over (partition by xDate order by xCount desc) rank_of_count
         from table_name) X
 where rank_of_count < 3

Upvotes: 3

StepUp
StepUp

Reputation: 38094

Just another one suggestion using DENSE_RANK():

DECLARE @FooTable TABLE
(
    xDate VARCHAR(25),
    xItem VARCHAR(10),
    xCount INT  
)

INSERT INTO @FooTable
(
    xDate,
    xItem,
    xCount
)
VALUES
  ('2018-01-01',   'A',           100)
, ('2018-01-01',   'B',           200)
, ('2018-01-01',   'D',           500)
, ('2018-01-02',   'C',           200)
, ('2018-01-02',   'E',           800)

SELECT 
s.*
FROM 
(
    SELECT 
      ft.xDate
    , ft.xItem
    , ft.xCount
    --, ROW_NUMBER() OVER(PARTITION BY ft.xDate ORDER BY ft.xCount DESC) rn
    , DENSE_RANK() OVER (PARTITION BY ft.xDate ORDER BY ft.xCount desc) dr
    FROM @FooTable ft
)s
WHERE s.dr < 3

Upvotes: 0

Will Kong
Will Kong

Reputation: 71

Another method is to use TOP, CROSS APPLY.

;WITH CTE AS
(
select 
distinct xDate
from Your_Table
)
SELECT 
T.xDate,
T1.xItem,
T1.xCount
FROM CTE T
CROSS APPLY (SELECT TOP 2 xItem,xCount FROM Your_Table WHERE xDate=T.xDate ORDER BY xCount DESC ) T1

Upvotes: 0

Praneet Nadkar
Praneet Nadkar

Reputation: 813

Well I would not use any function. it looks pretty straight forward to me. The quickest would be

SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

Check the full sample code here:

create table #temp (xDate datetime, xItem nvarchar(max), xCount int);

insert into #temp
select
'2018-01-01','A', 100 union all 
select '2018-01-01','B', 200 union all 
select '2018-01-01','D', 500 union all 
select '2018-01-02','C', 200 union all 
select '2018-01-02','E', 800


SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

drop table #temp;

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to use RANK window function, if there more the two row have same xCount then you want to get them all.

You can try to use dense_rank instead of RANK

SELECT xDate,xItem,xCount
FROM (
    SELECT *,RANK() OVER(PARTITION BY xDate ORDER BY xCount DESC) rn
    FROM T
) t1
WHERE t1.rn <= 2

Upvotes: 5

Related Questions