How to get only one record from left table against each record from right table

Question

I have two tables, one for some kind of devices and the other for their locations (using this table to keep a track record of location history). Tables' schemas are:

1. tbl_Devices:

   • DeviceSrNumber (bigint, not null, pk)
   • Some other columns

2. tbl_DeviceLocation

   • SrNumber (bigint, not null, pk, identity)
   • DeviceSrNumber (bigint, not null)
   • Location (varchar(300), not null)
   • AddDateTime (DateTime, not null)

What I need is DeviceSrNumber, last Location, last Location set AddDateTime

What I am doing now:

SELECT
    DeviceSrNumber,
    (
        SELECT TOP (1) Location
        FROM tbl_DeviceLocation
        WHERE (DeviceSrNumber = d.DeviceSrNumber)
        ORDER BY AddDateTime DESC
    ) AS 'Location',
    (
        SELECT TOP (1) AddDateTime
        FROM tbl_DeviceLocation
        WHERE (DeviceSrNumber = d.DeviceSrNumber)
        ORDER BY AddDateTime DESC
    ) AS 'AddDateTime '
FROM
    tbl_Devices AS d

But I need to do it with a join in a cleaner way, because according to my knowledge, a sub-query is not efficient and not recommended in a large-scale database.

Note: It is a small part of a large project, where it will deal with millions of records.

Answer 1

You can start with this :

SELECT *
FROM (
    SELECT        
      d.DeviceSrNumber
    , dl.Location    LastLocation
    , dl.AddDateTime LastLocationSetDateTime
    , ROW_NUMBER() OVER(PARTITION BY d.DeviceSrNumber ORDER BY AddDateTime DESC) RN 
    FROM 
        tbl_Devices d
    JOIN tbl_DeviceLocation dl ON dl.DeviceSrNumber = d.DeviceSrNumber 
) D 
WHERE 
    RN = 1

UPDATE

SELECT        
  d.DeviceSrNumber
, MAX(dl.Location)  LastLocation
, MAX(dl.AddDateTime) LastLocationSetDateTime
FROM 
    tbl_Devices d
JOIN tbl_DeviceLocation dl ON dl.DeviceSrNumber = d.DeviceSrNumber 
GROUP BY d.DeviceSrNumber
ORDER BY AddDateTime DESC