create the SQL query

Question

I have the table of DeviceMaster and DeviceStatus. where in DeviceMaster is the master for devices and the DeviceStatus is the status of the device.Now i want to get the record of the latest DeviceStatus of each device with only one row using the DeviceMasterId and according to the last one first(descending order).

eg.

 DeviceName       RecordCreatedDate        Status
ElectronicRod     14/11/2011 12:00:00       On
ElectronicRod     14/11/2011 11:30:00       Off

even though the there is multiple record in DeviceStatus.

here is the table structure

DeviceMaster

[Id] [int],
[ROId] [int] ,
[ClientId] [int] ,
[DeviceTypeId] [int] ,
[Label] [varchar](50) ,
[ClientCommChannelId] [int] ,
[ServerCommChannelId] [bigint] ,
[DeviceName] [varchar](50) ,
[Address] [varchar](50) ,
[Attribute1] [varchar](50) ,
[Attribute2] [varchar](50) ,
[Attribute3] [varchar](50) ,
[IsDeleted] [bit] ,
[RecordCreatedDate] [datetime] ,
[RecordUpdatedDate] [datetime] ,
[RecordCreatedBy] [int] ,
[RecordUpdatedBy] [int] ,
[IsTransfered] [bit]

DeviceStatus

[Id] [bigint],
[ROId] [int],
[ClientId] [int],
[ServerDeviceId] [bigint] , --It is the foreign key reference of Device Id
[ClientDeviceId] [int] ,
[Status] [bit] ,
[TimeStamp] [datetime] ,
[Attribute1] [varchar](50) ,
[Attribute2] [varchar](50) ,
[Attribute3] [varchar](50) ,
[RecordCreatedDate] [datetime] ,
[RecordUpdatedDate] [datetime] ,
[RecordCreatedBy] [int] ,
[RecordUpdatedBy] [int] ,
[IsTransfered] [bit]

DeviceStatus have the multiple line entry for single device.I need the latest DeviceStatus for each and every device.

Thank you in advance

Can you post the table structure? Also what have you tried yourself? do you have any code etc? — Purplegoldfish
– Purplegoldfish, Commented Nov 14, 2011 at 10:19
Can you clarify if the DeviceStatus is an audit table showing the history of status changes and not the current status. — Steve Henderson
– Steve Henderson, Commented Nov 14, 2011 at 10:26

marc_s · Accepted Answer · 2011-11-14 10:24:50Z

1

You can use a CTE (Common Table Expression) with the ROW_NUMBER function:

;WITH LastPerDevice AS
(
   SELECT 
       dm.DeviceName, ds.RecordCreatedDate, ds.Status,
       ROW_NUMBER() OVER(PARTITION BY dm.DeviceMasterId 
                         ORDER BY ds.RecordCreatedDate DESC) AS 'RowNum'
   FROM dbo.DeviceMaster dm
   INNER JOIN dbo.DeviceStatus ds ON ds.DeviceMasterId = dm.DeviceMasterId 
)
SELECT 
   DeviceName, RecordCreatedDate, Status
FROM LastPerDevice
WHERE RowNum = 1

This CTE "partitions" your data by DeviceMasterId, and for each partition, the ROW_NUMBER function hands out sequential numbers, starting at 1 and ordered by RecordCreatedDate DESC - so the most recent row gets RowNum = 1 (for each DeviceMasterId) which is what I select from the CTE in the SELECT statement after it.

answered Nov 14, 2011 at 10:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Henderson Over a year ago

Nice. If your tables are very large, I would suggest running a performance test on this. (run with SET STATISTICS IO ON). Compare with one of the other answers here and either see which gives the lowest IO / effort.

Brandon Moore · Accepted Answer · 2011-11-14 10:31:15Z

0

I'm not positive what you're looking for, but the following uses a subquery in the where clause to filter on the last date. Maybe if this isn't exactly what you're looking for it may help you on your way to finding it. Let me know if you need me to clarify anything.

    select DeviceName, RecordCreateDated, Status
    from   DeviceMaster dm join
           DeviceStatus ds on dm.Id = ds.ServiceDeviceId
    where  DeviceName = 'ElectronicRod' and
           RecordCreateDate = 
               (select  max(RecordCreatedDate)
                from    DeviceMaster dm1 join 
                        DeviceStatus ds1 on dm1.Id = ds1.ServiceDeviceId
                where   dm1.DeviceName = dm.DeviceName)

answered Nov 14, 2011 at 10:31

Brandon Moore

8,84015 gold badges69 silver badges124 bronze badges

Comments

kol · Accepted Answer · 2011-11-14 10:43:44Z

0

select dm.DeviceName, ds1.LatestDate, ds2.Status
from DeviceMaster dm,
  (select ServerDeviceId, max(RecordCreatedDate) as LatestDate
  from DeviceStatus
  group by ServerDeviceId) ds1,
  DeviceStatus ds2
where dm.Id = ds1.ServerDeviceId
  and dm.Id = ds2.ServerDeviceId
  and ds1.LatestDate = ds2.RecordCreatedDate
order by ds1.LatestDate desc

edited Nov 14, 2011 at 10:43

answered Nov 14, 2011 at 10:31

kol

28.9k13 gold badges91 silver badges126 bronze badges

1 Comment

kol Over a year ago

OK, I finished hammering this... Please try it :)

Steve Henderson · Accepted Answer · 2011-11-14 10:45:47Z

0

You want to get the status from the latest record in the Status table for each Device. This means you need to actually do two joins to the Status table - one table to get the latest row (grouping by the device), and then again to get the status for that latest status. Like this:

I see you have BIGINTS - this suggests to me that the Status table is large. This suggests it is updated very frequently. Make sure the Status table is indexed on ServerDeviceId.

select DeviceName ,ds.RecordCreatedDate,ds.Status
from 
DeviceMaster dm  inner join 
(select ServerDeviceId,MAX(Id) DeviceStatusId from DeviceStatus
group by ServerDeviceId) laststatus 
    on laststatus.ServerDeviceId= dm.Id inner join 
DeviceStatus ds 
    on ds.Id = laststatus.DeviceStatusId

answered Nov 14, 2011 at 10:45

Steve Henderson

5143 silver badges7 bronze badges

1 Comment

kol Over a year ago

This is almost the same as mine :) You did a better job by using max(Id) and joining the status tables on Id - I joined them on ServerDeviceId and creation date, which is a bit stupid. Add the descending ordering according to ds.RecordCreatedDate, and this solution becomes perfect :)

Moonlight · Accepted Answer · 2011-11-14 11:14:23Z

0

use the max() , group by method

your code will look probbly a bit like this:

 SELECT max("DeviceStatus") as "devicestatus" , "Id" FROM "DeviceMaster"
 group by "Id";

EDIT:

 SELECT max("RecordCreatedDate") as "Date" , "DeviceName" FROM "DeviceMaster"
 group by "Name";

edited Nov 14, 2011 at 11:14

answered Nov 14, 2011 at 10:35

Moonlight

7081 gold badge7 silver badges18 bronze badges

1 Comment

Steve Henderson Over a year ago

If it is a BIT, the max will always be 1 (unless the status is always 0). You don't want the MAX, you want the latest!

Collectives™ on Stack Overflow

create the SQL query

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related