query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

actuary
Starting Member

23 Posts

Posted - 2008-11-09 : 15:46:45

Hi,

I have a table with the following data:

OId Status Date
1035.1 1 2008-11-07 23:32:19.513
1024.1 1 2006-11-28 10:48:21.997
1024.1 2 2006-11-28 11:15:07.557
1024.1 3 2007-01-02 09:34:29.247
1024.1 4 2007-01-08 10:35:58.813
1033.1 1 2006-12-13 12:26:19.163
1033.1 2 2006-12-13 16:57:48.250

Now I want to get the latest status per order id (I check it against the max date as it is timestamped). I would appreciate help here.

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-09 : 15:52:27

select OID,max(status)as status,max(date)as maxdate
from table
group by OID

actuary
Starting Member

23 Posts

Posted - 2008-11-09 : 16:07:57

hi,

Thanks for the reply. This returns me the max status which is not what I require. I want only 1 row per order with the max status date (date column) since the status ids can both increase and decrease. Hope this helps.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-09 : 17:02:34

quote:
Originally posted by sodeep

select OID,~~max(status)as status,~~max(date)as maxdate
from table
group by OID

actuary
Starting Member

23 Posts

Posted - 2008-11-09 : 17:16:48

Hi, I would need the statusid for the max(date) as well :) thanks for your patience, but 1 more addition to the query and that should fix it!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-09 : 17:31:47

Did you try yourself?

Is this one????
select OID,statusid,max(date)as maxdate
from table
group by OID,statusid

actuary
Starting Member

23 Posts

Posted - 2008-11-09 : 18:18:32

Oh yes! and that's why I am here :) This returns back the all the statuses per order. I just want the top 1 per order, and that's where I am struggling. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 23:02:27

[code]SELECT t.*
FROM Table t
INNER JOIN (SELECT OId,MAX(Date) AS Latest
FROM Table
GROUP BY OId)t1
ON t1.OId=t.OId
AND t1.Latest=t.Date[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources