Select Most Recent - 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)

Select Most Recent

Author

Topic

k_shehadeh
Starting Member

3 Posts

Posted - 2007-10-10 : 16:00:41

Let's say you have a status table that looks like this:


DeviceID | DriveStatus | Time 
---------------------------------
   1          Bad       10/10/2007
   2          Bad       10/9/2007
   3          Good      10/9/2007
   1          Good      10/8/2007
   2          Good      10/7/2007

I need to write a query that gets the most recent DriveStatus for devices with ID 1 and 2. So the problem here is that I can't use ORDER BY DESC on Time because there are multiple devices here. So how do I pick out the most recent status for devices 1 and 2?

This problem has been plaguing me for a while and I'd like to get a solution once and for all.

Thanks!

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 16:18:00

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90779#340258

which was pretty similar question

Kristen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-10 : 16:35:33

Be careful with that column that looks like a date

This query produces two different results if Time is a varchar and time as datetime.

SELECT a.DeviceID,a.DriveStatus,a.Time
FROM
@tbl a
INNER JOIN
(
select DeviceId
,[Time] = max(time)
from @tbl
where deviceid < 3
group by DeviceID
) b

ON
a.deviceid = b.deviceid
and a.time = b.time

Jim

k_shehadeh
Starting Member

3 Posts

Posted - 2007-10-11 : 13:49:03

Thanks very much for the info. That thread pointed me down the right path - I should note that I have the added obstacle of having to make these queries work with Access as well which forced me to tweak the final query. But all is well - at least it looks like it is.

Thanks again.

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 15:26:12

"I have the added obstacle of having to make these queries work with Access as well"

Best to ask questions in the Access forum, rather than the "Transact-SQL (2005)" forum please.

Kristen

k_shehadeh
Starting Member

3 Posts

Posted - 2007-10-11 : 15:57:01

Hm. But it needs to work with both SQL and Access.

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 16:50:54

Hopefully the answer I posted earlier helped with your SQL Solution. Sorry, but I have no knowledge of how to help with the Access solution.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources