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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL to get latest result and pull only one line fo

Author

Topic

Teamlink31
Starting Member

4 Posts

Posted - 2008-02-07 : 19:25:52

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

hey001us
Posting Yak Master

185 Posts

Posted - 2008-02-07 : 20:25:31

try this?
select max(LastEditDate), OrdResc, 'PG'+ cast(max(cast(right(po,5) as integer(3))) as char(7)) PO
from (
select '01/18/2008' LastEditDate, '130-100218' OrdResc, 'PG00251' po union
select '02/04/2008', '130-100218', 'PG00252' union
select '02/21/2007', '130-100218', 'PG15477' union
select '02/21/2005', '130-100218', 'PG12577' union
select '01/24/2004', '130-100218', 'PG12489' union
select '01/22/2003', '130-100214', 'PG12188' union
select '02/10/2001', '130-100214', 'PG78892' union
select '12/10/2005', '130-100214', 'PG98612' )xxx
group by OrdResc

hey

Teamlink31
Starting Member

4 Posts

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

quote:
Originally posted by hey001us

try this?
select max(LastEditDate), OrdResc, 'PG'+ cast(max(cast(right(po,5) as integer(3))) as char(7)) PO
from (
select '01/18/2008' LastEditDate, '130-100218' OrdResc, 'PG00251' po union
select '02/04/2008', '130-100218', 'PG00252' union
select '02/21/2007', '130-100218', 'PG15477' union
select '02/21/2005', '130-100218', 'PG12577' union
select '01/24/2004', '130-100218', 'PG12489' union
select '01/22/2003', '130-100214', 'PG12188' union
select '02/10/2001', '130-100214', 'PG78892' union
select '12/10/2005', '130-100214', 'PG98612' )xxx
group by OrdResc

hey

Teamlink31
Starting Member

4 Posts

Posted - 2008-02-07 : 23:09:10

hey,
Thanks for the information. However, the sql did return just two rows but I need the orginal sql that I posted in the correct format due to the info that I posted was just a sample. Can you put my orignal sql statement below in the correct text so it will return only one line per ordResc? Thanks so much!

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'
order by podel.OrdResc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 23:49:27

Answered here:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96998

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources