CODEWITHSUNDEEP
sqlsql-server
Purvesh Desai
Purvesh Desai

Reputation: 1805

sql top 1 based on group records

Table ProductMst (combination is unique - CodeNum, MainCatid, SubCatid)

id  CodeNum     MainCatid   SubCatid    Desc        Qty
1   001         1           1           prod1       5
2   001         1           2           prod2       10
3   001         2           3           prod3       1
4   002         2           3           prod4       2
5   003         2           3           prod5       3
6   004         1           1           prod6       4

Sub Table SubCatMst

id  name    subcode
1   scat1   00
2   scat2   00
3   scat3   02
4   scat4   03

Desired Result

id  CodeNum     MainCatid   SubCatid    Desc        Qty     subcode
2   001         1           2           prod2       15      00
3   001         2           3           prod3       1       02
4   002         2           3           prod4       2       02
5   003         2           3           prod5       3       02
6   004         1           1           prod6       4       00

Basically, i wanted to group subcode if same and sum (Qty) based on subcode. And, take the TOP 1 records data as all columns based on MAX(Qty).

Summary: Please check first 2 records which combines.

Query Attempt:

select * from (
select A.*, B.subcode, 
ROW_NUMBER() OVER( PARTITION BY A.CodeNum, A.MainCatid, B.subcode ORDER BY A.Qty desc) as row 
from ProductMst A 
inner join SubCatMst B on A.SubCatid=B.id
) as A where row<=1

Upvotes: 2

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Your question says:

i wanted to group subcode if same

If what is the same? I assume this means if codenum is the same.

Regardless of the exact field(s), the logic is the same. You can use window functions to aggregate the data and determine the row to choose:

select ps.*
from (select p.*, sc.subcode,
             sum(qty) over (partition by codenum, subcode) as qty,
             row_number() over (partition by codenum, subcode order by qty desc) as seqnum
      from ProductMst p join
           SubCatMst sc
           on p.subcatid = sc.id
     ) ps
where seqnum = 1;

Upvotes: 3

Related Questions