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 2000 Forums

Transact-SQL (2000)

Get the Current Status

Author

Topic

zaidrahman
Starting Member

5 Posts

Posted - 2008-06-05 : 12:54:01

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-05 : 13:38:58

try

Select t1.col1, max(t2.col1), max(t2.datecol) from table1 t1 inner join table2 t2 on t1.keycol=t2.keycol
group by t1.col1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 13:43:58

Not sure if OP meant this?

SELECT t1.OrderID,t2.StatusID,t2.InsertDate
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.OrderID=t1.OrderID
INNER JOIN (SELECT OrderID,MAX(InsertDate) as MaxDate
            FROM Table2
            GROUP BY OrderID)t3
ON t3.MaxDate=t2.InsertDate
AND t3.OrderID=t2.OrderID

zaidrahman
Starting Member

5 Posts

Posted - 2008-06-05 : 13:58:10

Thank you very much. Works like a champ.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-05 : 16:21:48

Use visakh's code, please. I don't think madhivanan's is what you are looking for.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources