MAX problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MAX problem

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources