SQL to get latest result and pull only one line fo

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

SQL to get latest result and pull only one line fo

Author

Topic

Teamlink31
Starting Member

4 Posts

Posted - 2008-02-07 : 19:23:00

I found an answer on your web site but it was for SQL 2005 and I am using SQL 2000. The question was posted on 2/7/08 from genelam ---- SQL to get latest result from each ID. Below is my sql:

select distinct
po.LastEditDate,
podel.OrdResc,
po.po

from Protean.dbo.poPurchaseOrder AS po
inner join protean.dbo.poline poln
on poln.parentobjectid = po.objectid
inner join protean.dbo.podelivery podel
on podel.parentobjectid = poln.objectid
and po.posite= 'LIG'
and podel.OrdRescSite = 'LIG'
order by podel.OrdResc

SQL would return the data below:
LastEditDate OrderResc PO

01/18/2008 130-100218 PG00251
02/04/2008 130-100218 PG00252
02/21/2007 130-100218 PG15477
02/21/2005 130-100218 PG12577
01/24/2004 130-100218 PG12489
01/22/2003 130-100214 PG12188
02/10/2001 130-100214 PG78892
12/10/2005 130-100214 PG98612

I need the SQL to give the result below by pulling only one PO for that OrderResc number with the latest and greatest LastEditDate and the result should be below:

LastEditDate OrderResc PO
02/04/2008 130-100218 PG00252
12/10/2005 130-100214 PG98612

This should be easy but I can't get an solution from anyone ---- please some HELP! Thanks, Deb

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-07 : 21:58:57

Try this...

SELECT OrderResc, PO, LastEditDate
FROM
(
SELECT podel.OrderResc, po.PO, po.LastEditDate,
ROW_NUMBER() OVER (PARTITION BY podel.OrderResc ORDER BY po.LastEditDate DESC) as REC
FROM Protean.dbo.poPurchaseOrder AS po
INNER JOIN protean.dbo.poline poln
ON poln.parentobjectid = po.objectid
INNER JOIN protean.dbo.podelivery podel
ON podel.parentobjectid = poln.objectid
WHERE po.posite= 'LIG'
AND podel.OrdRescSite = 'LIG'
) a
WHERE a.Rec = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 22:58:38

quote:
Originally posted by tprupsis

Try this...

SELECT OrderResc, PO, LastEditDate
FROM
(
SELECT podel.OrderResc, po.PO, po.LastEditDate,
ROW_NUMBER() OVER (PARTITION BY podel.OrderResc ORDER BY po.LastEditDate DESC) as REC
FROM Protean.dbo.poPurchaseOrder AS po
INNER JOIN protean.dbo.poline poln
ON poln.parentobjectid = po.objectid
INNER JOIN protean.dbo.podelivery podel
ON podel.parentobjectid = poln.objectid
WHERE po.posite= 'LIG'
AND podel.OrdRescSite = 'LIG'
) a
WHERE a.Rec = 1

ROW_NUMBER wont work is SQL 2000. Use this:-

select t.LastEditDate,t.OrdResc,t.po
from
(
select 
po.LastEditDate,
podel.OrdResc,
po.po

from Protean.dbo.poPurchaseOrder AS po
inner join protean.dbo.poline poln
on poln.parentobjectid = po.objectid
inner join protean.dbo.podelivery podel
on podel.parentobjectid = poln.objectid
and po.posite= 'LIG'
and podel.OrdRescSite = 'LIG'
)t
INNER JOIN 
(
select 
max(po.LastEditDate) as LastEditDate,
podel.OrdResc

from Protean.dbo.poPurchaseOrder AS po
inner join protean.dbo.poline poln
on poln.parentobjectid = po.objectid
inner join protean.dbo.podelivery podel
on podel.parentobjectid = poln.objectid
and po.posite= 'LIG'
and podel.OrdRescSite = 'LIG'
group by podel.OrdResc
)t1
on t1.OrdResc=t.OrdResc
and t1.LastEditDate=t.LastEditDate
order by t.OrdResc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources