Author |
Topic |
daidaluus
Yak Posting Veteran
73 Posts |
Posted - 2009-02-15 : 03:26:18
|
I have the following table and data
CREATE TABLE tbl (itemid int, num int, dayy int, qty int)
INSERT INTO tbl SELECT 1, 1, 1, 13 UNION ALL SELECT 1, 2, 2, 11 UNION ALL SELECT 2, 3, 1, 32 UNION ALL SELECT 2, 4, 2, 20 UNION ALL SELECT 2, 5, 2, 18 UNION ALL SELECT 3, 6, 1, 19 UNION ALL SELECT 3, 7, 1, 14
I want to get max qty of the max dayy for each itemid. In some dayys there can be more than one row for a unique itemid.
I want to get the following result
itemid num dayy qty ------ --- ---- --- 1 2 2 11 2 4 2 20 3 6 1 19
How can I do it?
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-15 : 05:56:46
|
select itemid, num, dayy, qty from ( select itemid, num, dayy, qty, row_number() over (partition by itemid order by dayy desc, qty desc) as recid from tbl) as f where recid = 1
E 12°55'05.63" N 56°04'39.26" |
 |
|
daidaluus
Yak Posting Veteran
73 Posts |
Posted - 2009-02-15 : 06:46:43
|
How can I do it in SQLServer 2000? |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-15 : 08:37:22
|
Use two derived tables, one for max dayy, and one for max qty.
E 12°55'05.63" N 56°04'39.26" |
 |
|
daidaluus
Yak Posting Veteran
73 Posts |
Posted - 2009-02-15 : 09:03:10
|
I tried but I couldnt build the query. can you show me how to do it? |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-15 : 09:43:04
|
select q.itemid, q.dayy, q.qty, w.num from( select d.itemid, d.dayy, max(f.qty) as qty from( select itemid, max(dayy) as dayy from tbl group by itemid) as d inner join tbl as f on f.itemid = d.itemid and f.dayy = d.dayy group by d.itemid, d.dayy) as q inner join tbl as w on w.itemid = q.itemid and q.dayy = q.dayy and q.qty = w.qty
E 12°55'05.63" N 56°04'39.26" |
 |
|
|
|
|