SELECT help - 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

SELECT help

Author

Topic

Russ1984
Starting Member

4 Posts

Posted - 2008-10-16 : 06:13:42

Hi,

Can someone please help me with the following select statement. If I have the table:

deviceStatusId, date, deviceId
1, 01/01/01, 1
2, 02/01/01, 1
3, 01/01/01, 2
4, 02/01/01, 2

If I have a table of deviceId's (from say: SELECT deviceId FROM PORTAL WHERE deviceId = @deviceId), returning Id's 1 & 2, how can I use this to select the deviceStatusId's that are most recently corresponding to these deviceId's.

It's a bit of a mouthful but essentially I'm after deviceStatusId's 2 and 4 here. Maybe it's something like:

SELECT deviceStatusId FROM deviceStatus WHERE (????) AND deviceId IN (SELECT deviceId from Device WHERE portalId = @portalId).

Thanks in advance for any help I recieve.

Russ.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-16 : 06:24:19

Something like...


SELECT
	a.[deviceStatusId]
FROM
	(
	SELECT
		MAX([date]) AS highest_Date
		, [deviceId] AS devId
	FROM
		<theTable>
	GROUP BY
		[deviceId]
	)
	ms
	
	JOIN <theTable> a ON
		a.[deviceId] = ms.[devId] 
		AND a.[date] = ms.[highest_date]

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources