Get multiple MAX with where criteria

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

General SQL Server Forums

New to SQL Server Programming

Get multiple MAX with where criteria

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2015-03-17 : 23:49:03

After looking at the web I have the idea this problem may be solved by use of Row_Number() over ( partition... but I dont understand how.

Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1

But I wanted to return
CustomerID, PartNum, MaxQtyOrderedNotShipped

That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0

If I use this:

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1

there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0

What to try ?

Thanks.

andrewcw

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 01:53:06

Try this:

select customerid
      ,partnum
      ,max(qtyinorder) as max_qtyinorder
  from silk.dbo.maxofgroup
 where shipped=0
 group by customerid
         ,partnum

andrewcw
Posting Yak Master

133 Posts

Posted - 2015-03-19 : 00:46:38

Thank you !!! That's exactly what I needed !!!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources