Get the Current Status - 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)

Get the Current Status

Author

Topic

zaidrahman
Starting Member

5 Posts

Posted - 2008-06-05 : 12:04:16

Hello All,

Can someone help me with this query?

I have the following table and data structure

Table1: Orders
Field1: OrderID

Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate

Sample Data:
Table1:
11
12

Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008

Wanted Results:
11,4,6/4/2008
12,2,6/2/2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 12:36:14

[code]DECLARE @OrderStatus TABLE (OrderStatusID INT, OrderID INT, StatusID INT, InsertDate DATETIME)

SET DATEFORMAT MDY

INSERT @OrderStatus
SELECT 1, 11, 1, '6/1/2008' UNION ALL
SELECT 2, 12, 1, '6/1/2008' UNION ALL
SELECT 3, 11, 2, '6/2/2008' UNION ALL
SELECT 4, 12, 2, '6/2/2008' UNION ALL
SELECT 5, 11, 3, '6/3/2008' UNION ALL
SELECT 6, 11, 4, '6/4/2008'

SELECT OrderID,
StatusID,
InsertDate
FROM (
SELECT OrderID,
StatusID,
InsertDate,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY InsertDate DESC) AS RecID
FROM @OrderStatus
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

zaidrahman
Starting Member

5 Posts

Posted - 2008-06-05 : 12:47:25

Thank you both for the reply.

I am getting an error of
'ROW_NUMBER' is not a recognized function name.

zaidrahman
Starting Member

5 Posts

Posted - 2008-06-05 : 12:52:15

I guess I should have mentioned I am using SQL 2000. And I now see I am in the wrong forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources