Second last in sql along with last 1

[coolstrike23]

Hi All

I have one table as below

POSeq Line Num ItemNO UnitCost UnitConv Date

1234 456 XYZ 20 10 2010,10,10
1255 496 XYZ 30 10 2010,10,20
1256 497 ABC 20 10 2010,10,21

Query to return

ItemNo Prevprice Prevdate CurrentPrice Currentdate
XYZ 2.0 10/10/2010 3.0 20/10/2010

Appreciate any help.

Thanks

 

[bborissov]

SQL Server 2005 or 2008:

SELECT * 
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Date DESC) AS Test
             FROM YourTable) Tbl1
WHERE Test = 2

2000:

SELECT TOP 1 * 
FROM (SELECT TOP 2 * FROM YourTAble
             ORDER BY Date DESC) Tbl1
ORDER BY Date
[code]



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

SQL 2005+ solution:

;with cte as (select *, row_number() over (partition by ItemNo
order by Date DESC) as Row from ItemsInfo)

select T.ItemNo, P.Price as PrevPrice, P.[Date] as PrevDate,
T.Price as CurrentPrice, T.[Date] as CurrentDate
from Cte T LEFT JOIN cte P on T.ItemNo = P.ItemNo and T.Row = 1
and P.Row = 2

PluralSight Learning Library

 

[coolstrike23]

markros, neat query, does exactly what i wanted.

Thank you