Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MAX order number???

Status
Not open for further replies.

cfcProgrammer

Programmer
Mar 1, 2006
88
CA
Hi Everyone,

Thanks in advance for any help you can provide.

I have a query that will bring back all the orders for a particular serial number but I only need to bring back the last order number and it's execution date. I'm not sure how to achieve this by using MAX(we do not have to use MAX but that is how I thought the data could be retrieved easily).

SELECT
ma.Id as 'Mobile ID'
,ma.[MobileType]
,ma.[CustomerId]
,ma.[ICCID]
,ma.[IMSI]
,ma.[Status]
,ma.[ARAccountCode]
,ord.OrderId as 'Last Order Number'
,ord.ExecutionDate

FROM Mobile ma, OrderDetails od, Order ord

where ma.ICCID = od.SerialNum

and od.OrderId = ord.OrderId

and ma.ARAccountCode in('xxx','abc','def','ghi','zzz')

order by ma.ARAccountCode, SerialNum, ord.OrderId


cfcProgrammer
 
Personally, I would use the row_number function.

Code:
; With Data As
(
SELECT  ma.Id as 'Mobile ID'
       ,ma.[MobileType]
       ,ma.[CustomerId]
       ,ma.[ICCID]
       ,ma.[IMSI]
       ,ma.[Status]
       ,ma.[ARAccountCode]
       ,ord.OrderId as 'Last Order Number'
       ,ord.ExecutionDate,
       Row_Number() Over (Partition By SerialNum Order By ExecutionDate DESC) As RowId
FROM   Mobile ma, OrderDetails od, [Order] ord
where  ma.ICCID = od.SerialNum
       and od.OrderId = ord.OrderId
       and ma.ARAccountCode in('xxx','abc','def','ghi','zzz')
)
Select *
From   Data
Where  RowId = 1
order by ma.ARAccountCode, SerialNum, ord.OrderId

The magic happens here:

Row_Number() Over (Partition By SerialNum Order By ExecutionDate DESC) As RowId

This will return an incrementing number. The numbers will start at 1. Whenever the SerialNum changes, this value will restart at 1 (this is what the Partition by clause does). The ordering of the numbers is based on the Order By. In this case ExecutionDate DESC so that the most recent date will have the value 1.

Next, notice how I use a common table expression. This allows me to put a filter on the Row_Number function.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
OK.. So I realized I didn't have a group by statement

I have added this to the query

GROUP BY ma.ARAccountCode,MA.ICCID, MA.CustomerId, IMSI,ord.ExecutionDate,MA.ID,MA.MobileType, MA.Status
order by ma.ARAccountCode, MA.ICCID

but I am still not getting the max order number, I'm getting multiple orders for each serial number,

cfcProgrammer
 
gmmastros

Thank you so much, I tried your suggestion and it works perfectly and runs much faster as well!!

Thanks again

Have a great Day!! [bigsmile]

cfcProgrammer
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top