CODEWITHSUNDEEP
sqlsql-serversql-server-2008
gofor.net
gofor.net

Reputation: 4298

create the SQL query

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

Upvotes: 0

Views: 93

Answers (5)

Moonlight
Moonlight

Reputation: 708

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";

Upvotes: 0

Steve Henderson
Steve Henderson

Reputation: 514

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

Upvotes: 0

kol
kol

Reputation: 28688

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

Upvotes: 0

Brandon Moore
Brandon Moore

Reputation: 8780

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)

Upvotes: 0

marc_s
marc_s

Reputation: 754468

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.

Upvotes: 1

Related Questions