to get max record - 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 2005 Forums

Transact-SQL (2005)

to get max record

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-07-31 : 09:45:54

i want like if same purchase order is 3 times in table then whatever is current one that i want..i want to retrive the max record from so what will be the query ? this what i have
but its not working

SELECT MAx(Orders.[Customer Name]) AS [LastOfCustomer Name],
MAx(Orders.ScanDate) AS LastOfScanDate, max(Orders.SONumber)
AS LastOfSONumber, Orders.PurchaseOrderNumber,
max(Orders.CustomerComment) AS LastOfCustomerComment,
max(Orders.ScanTime) AS LastOfScanTime,
max(Orders.[Axapta Customer No]) AS AccountNo
FROM Orders
GROUP BY Orders.PurchaseOrderNumber

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 09:47:47

Can u tell u'r issue with some sample data's
it would be easier to understand.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 09:48:49

[code]select *
from
(
select *, row_no = row_number() over (partition by PurchaseOrderNumber order by ScanDate desc)
from Orders
) a
where a.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-31 : 09:49:34

Assuming that SONumber is what you want the max of then you'd have to select the max sonumber and group by order number, then join back. Something like this:

select Orders.CustomerName,Orders.ScanDate,base.SoNumber,base.OrderNumber
from Orders O
inner join(select max(SoNumber) as SoNumber,OrderNumber
FROM Orders O Group by OrderNumber)base
on o.SoNumber=base.soNumber

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources