Reputation: 6251
I have table in SQL Server containing sales data as follows.
seq_no Itemid Unit pcs value
------------------------------------
1 501 101 1 1001.48
1 502 102 2 1004.25
1 502 102 7 987.58
1 503 103 3 787.58
1 503 103 7 647.87
1 503 103 9 1478.58
1 504 104 2 202.25
1 504 104 3 365.87
1 504 104 7 102.25
1 504 104 6 322.22
1 505 105 1 2000.01
1 505 105 2 914.02
Now I want unit wise maximum value records. Means when I grouping on seq_no, itemid, unit
and getting summing of pcs, and value, its giving result as follows.
Itemwise Summary
seq_no itemid unit pcs value
------------------------------------
1 501 101 1 1001.48
1 502 102 9 1991.83
1 503 103 19 2914.03
1 504 104 12 992.59
1 505 105 3 2914.03
Now you can see, I have two different records which has maximum value (2914.03) (eg. 3rd and 5th record). I need 3rd record because It has maximum value with maximum pcs. In my case I want following:
seq_no itemid unit pcs value
-------------------------------------
1 503 105 19 2914.03
How do I get this result, without harming performance, because this table has so many rows?
Upvotes: 0
Views: 188
Reputation: 11556
Try this.
SELECT TOP 1 seq_no,
item_id,unit,
SUM(pcs) AS 'pcs',
SUM(value) AS 'value'
FROM tbl1
GROUP BY item_id,seq_no,unit
ORDER BY value DESC,pcs DESC
Upvotes: 2
Reputation: 1540
Try,
CREATE TABLE tbl1(seq_no int,item_id int,Unit int,pcs int,value bigint);
insert into tbl1 VALUES(1,501,101,1,1001.48);
insert into tbl1 VALUES(1,502,102,2,1004.25);
insert into tbl1 VALUES(1,502,102,7,987.58);
insert into tbl1 VALUES(1,503,103,3,787.58);
insert into tbl1 VALUES(1,503,103,7,647.87);
insert into tbl1 VALUES(1,503,103,9,1478.58);
insert into tbl1 VALUES(1,504,104,2,202.25);
insert into tbl1 VALUES(1,504,104,3,365.87);
insert into tbl1 VALUES(1,504,104,7,102.25);
insert into tbl1 VALUES(1,504,104,6,322.22);
insert into tbl1 VALUES(1,505,105,1,2000.01);
insert into tbl1 VALUES(1,505,105,2,914.02);
select * from tbl1;
with cte(seq_no,a,b,c,d)as(
select x.seq_no ,x.item_id,x.Unit,sum(x.pcs),sum(x.value) from (
select seq_no,item_id,Unit,pcs,value ,
ROW_NUMBER() over (partition by unit order by value) as rnk
from tbl1
)x group by x.seq_no,x.item_id,x.Unit
)select seq_no,sum(a)/5 as item_id,MAX(b)as Unit, MAX(c)as pcs,MAX(d)as value from cte
group by seq_no
This one is working correct ple check.
Upvotes: -1
Reputation: 1
try this.
WITH res AS (
SELECT ROW_NUMBER()OVER (ORDER BY seq_no)rnum,
seq_no,ItemID,unit,SUM(pcs)sum_pcs,SUM(VALUE)sum_val FROM tbl_name
GROUP BY seq_no,ItemID,unit
)
SELECT TOP 1 * FROM res ORDER BY sum_pcs DESC,sum_val DESC
Upvotes: 0
Reputation: 24803
SELECT TOP 1 *
FROM itemwise_summary
ORDER BY value desc, pcs desc
Upvotes: 1