CODEWITHSUNDEEP
sqlsql-server
user1587872
user1587872

Reputation: 365

SQL:selecting top record for a group of column

I need to select top record with max value for a group of other columns. In the sample data below, i need to select top record with max 'Weightage' for each group of 'id', 'ItemType' 

 create table sampleTable(id int, ItemType varchar(10), ItemCode varchar(10), Weightage int)
insert into sampleTable values(1, 'A','aaa',2)
 insert into sampleTable values(1, 'A','bbb',3)
insert into sampleTable values(1, 'A','ccc',3)
insert into sampleTable values(1, 'B','ddd',1)
insert into sampleTable values(1, 'B','eee',2)
insert into sampleTable values(2, 'A','aaa',1)

The expected output should be

id          ItemType    ItemCode
 --------------------------------
 1          A           bbb
1           B           eee
2           A           aaa

I tried as follows

SELECT top 1 id, ItemType,ItemCode
FROM sampleTable  WITH(NOLOCK)  
GROUP BY id,ItemType,ItemCode,Weightage
ORDER BY Weightage desc

But it is not giving expected result. Thanks

Upvotes: 3

Views: 84

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one way using ROW_NUMBER

SELECT TOP 1 WITH ties id, 
                       itemtype, 
                       itemcode 
FROM   sampletable WITH(nolock) 
GROUP  BY id, 
          itemtype, 
          itemcode, 
          weightage 
ORDER  BY Row_number()OVER(partition BY id, itemtype ORDER BY weightage DESC)

TOP 1 with TIES will return the records with Tie based on Order by

Hope you know the meaning of using WITH(NOLOCK) hint. It will pull uncommitted data as well

Upvotes: 3

sgeddes
sgeddes

Reputation: 62861

Here's one option using row_number():

select * 
from (
    select *, row_number() over (partition by id, itemtype order by Weightage desc) rn
    from sampletable
) t
where rn = 1

Upvotes: 2

Related Questions