CODEWITHSUNDEEP
sqlsql-serversql-server-2008
krsh
krsh

Reputation: 40

SQL Server : how to select top 2 records of the same group?

I'm using SQL Server 2008. I have a problem where I need to get the top 2 records in a group determined by 2 columns Code and Period, that will then be inserted to another table.

Here is a sample table:

Code  |  Period  |  Dept  | DeptWorkPartSum
001      2013-11    D1      53
001      2013-11    D2      33
001      2013-11    D3      12
002      2013-11    D2      30
002      2013-11    D4      28
002      2013-11    D5      15
002      2013-12    D2      100

And what I want to get out of it is:

Code  |  Period  |  Dept  | DeptWorkPartSum
001      2013-11    D1      53
001      2013-11    D2      33
002      2013-11    D2      30
002      2013-11    D4      28
002      2013-12    D2      100

It is probably a very simple solution but I can't figure it out at the moment.

Upvotes: 1

Views: 3219

Answers (3)

vhadalgi
vhadalgi

Reputation: 7189

try this:


with cte as
(
select *,ROW_NUMBER() over (partition by Code,Period,Dept order by Code) as rn from table
)

select Code,Period,Dept,DeptWorkPartSum from cte where rn<=2

fiddle demo

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460028

A simple approach is using a CTE with ROW_NUMBER ranking function:

WITH CTE AS
(
    SELECT Code,Period,Dept,DeptWorkPartSum,
           RN = ROW_NUMBER() OVER (PARTITION BY Code,Period, Dept
                                   ORDER BY DeptWorkPartSum)
    FROM dbo.TableName
)
SELECT Code,Period,Dept,DeptWorkPartSum
FROM CTE
WHERE RN <= 2

Demo

However, your desired result seems to be incorrect since Dept is different for all three codes.

Maybe you don't want to group by Dept but order by it, then the result is correct:

WITH CTE AS
(
    SELECT Code,Period,Dept,DeptWorkPartSum,
           RN = ROW_NUMBER() OVER (PARTITION BY Code,Period
                                   ORDER BY Dept, DeptWorkPartSum)
    FROM dbo.TableName
)
SELECT Code,Period,Dept,DeptWorkPartSum
FROM CTE
WHERE RN <= 2

Demo

Upvotes: 2

Twinkles
Twinkles

Reputation: 1994

SELECT Code, Period, Dept, DeptWorkPartSum
FROM (
  SELECT Code, Period, Dept, DeptWorkPartSum
    ,ROW_NUMBER ( ) OVER ( PARTITION BY Code, Period, Dept ORDER BY DeptWorkPartSum DESC) r
  FROM table) q
WHERE r <= 2

Upvotes: 0

Related Questions