CODEWITHSUNDEEP
sqlsql-server-2008select
joce
joce

Reputation: 9892

Select many TOP 1

In an SQL Server 2008 database, I have tables such as:

CREATE TABLE t_DeviceType
(
    ID INTEGER PRIMARY KEY IDENTITY, 
    Device VARCHAR(32) NOT NULL 
)

CREATE TABLE t_Device
(
    ID INTEGER PRIMARY KEY IDENTITY, 
    DeviceTypeID INTEGER NOT NULL,
    IPAddress INTEGER NOT NULL,
    RegTime DATETIME NOT NULL,
    IsActive BIT NOT NULL
)

Now I want a query that would pull the last registered, still active device (if any), for all device types.

For a single, given device, that'd be easy:

SELECT TOP 1 *
FROM t_Device
WHERE IsActive = 1
  AND DeviceTypeID = 42 -- For example
ORDER BY RegTime DESC

However, I need that for all possible valued of DeviceTypeID.

I thought I could do it with a JOIN statement, but there's no way I found to limit the number of records pulled by a JOIN effectively.

I tried the following:

SELECT t_DeviceType.ID AS TypeID, 
       t_DeviceType.Device, 
       Device.ID AS DeviceID, 
       Device.IPAddress AS IPAddress
FROM t_DeviceType
JOIN ( 
    SELECT TOP 1 t_Device.ID, DeviceTypeID, IPAddress 
    FROM t_Device
    JOIN t_DeviceType ON DeviceTypeID = t_DeviceType.ID
    WHERE IsActive = 1
    ORDER BY RegTime DESC
) AS Device ON Device.ConsoleTypeID = t_DeviceType.ID

But that only returned the last registered devide of any type.

Anybody has a suggestion?

Thanks,

Upvotes: 1

Views: 208

Answers (2)

Andriy M
Andriy M

Reputation: 77677

In SQL Server 2008 you can also use ranking to achieve what you want:

SELECT
  t.ID AS TypeID,
  t.Device,
  d.ID AS DeviceID,
  d.IPAddress
FROM (
 SELECT
   *,
   rnk = RANK() OVER (PARTITION BY DeviceTypeID ORDER BY RegTime DESC)
 FROM t_Device
) d
  INNER JOIN t_DeviceType t ON d.DeviceTypeID = t.ID
WHERE d.rnk = 1

Upvotes: 0

Derek
Derek

Reputation: 23228

You want to get a list of Devices along with their associated maximum RegTime.

SELECT DeviceTypeID, max(RegTime) as LatestRegTime
FROM t_Device
WHERE IsActive = 1
GROUP BY DeviceTypeID

Now you can use this as a derived table and join to it (instead of the select top 1 you tried):

SELECT t_DeviceType.ID AS TypeID, 
       t_DeviceType.Device, 
       Device.ID AS DeviceID, 
       Device.IPAddress AS IPAddress
FROM t_DeviceType
JOIN ( 
    SELECT t_Device.ID, 
           IPAddress, 
           DeviceTypeID, 
           max(RegTime) as LatestRegTime
    FROM t_Device
    WHERE IsActive = 1
    GROUP BY t_Device.ID, IPAddress, DeviceTypeID
) AS Device ON Device.DeviceTypeID = t_DeviceType.ID

Upvotes: 5

Related Questions