MAX order number???

[cfcProgrammer]

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

 

[gmmastros]

Personally, I would use the row_number function.

; 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

 

[cfcProgrammer]

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

 

[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!!

cfcProgrammer