Selecting distinct values from multiple columns with multiple tables

Question

SEE EDITS BELOW

I am trying to build a list in SQL Server 2012 that shows this:

Serial    FW_Ver  DeviceName  UserName  LastUpload           LastRecord
12345678  998     Test1       User1     2016-02-22 17:31:28  2016-02-19 11:14:15
45678912  998     Test2       User5     2016-03-14 16:24:17  2016-03-14 12:24:57
78978979  600     Test3       User12    2016-01-31 09:12:48  2016-01-30 23:49:12
45554468  NULL    Test4       NULL      NULL                 NULL

This is built from 3 different tables within the same database. The serial numbers within the device table are all unique with no duplicates so I want to see results returned for all serial numbers even if they haven't uploaded any files yet. My current query is giving me the results I want, but with some unwanted additions as shown below (first 3 rows):

Serial    FW_Ver  DeviceName  UserName  LastUpload           LastRecord
12345678  772     Test1       User1     2016-02-22 17:30:18  2014-01-01 16:19:06
12348678  891     Test1       User1     2016-02-22 17:30:31  2015-02-16 15:41:56
12345678  998     Test1       User1     2016-02-22 17:31:28  2016-02-19 11:14:15
45678912  998     Test2       User5     2016-03-14 16:24:17  2016-03-14 12:24:57
78978979  600     Test3       User12    2016-01-31 09:12:48  2016-01-30 23:49:12
45554468  NULL    Test4       NULL      NULL                 NULL

As you can see, I am getting the most recent datetimes for a specific device for each version of firmware it has used. I don't want that, I only want the most recent datetime listed from the most recent file regardless of firmware version.

Here is my query thus far:

SELECT d.Serial,
de.Firmware AS FW_Ver,
d.DeviceName,
MAX(de.CreatedOn) AS LastUploadedDate,
MAX(de.EventTimeStamp) AS LastRecordingDate

FROM AuroraCore.dbo.DeviceEvent AS de
RIGHT JOIN AuroraCore.dbo.Device AS d ON d.Id = de.DeviceId

GROUP BY d.Serial, de.firmware, d.DeviceName

I tried using MAX(de.Firmware) OVER (PARTITION BY d.serial) AS FW_Ver, but that returns the same number of results and the firmware version is then only shown as the highest number for that given device (998 x3 instead of 772, 891, and 998 as displayed in the top 3 rows in the above table).

How can I pull the datetimes from ONLY the most recent file(s) for a given device and return the corresponding version of firmware? I want the precedence on the uploaded date followed by the recorded date. Thanks!

EDIT 1: I can get rid of the extra, unwanted info by using this:

SELECT d.Serial, 
t.Firmware AS FW_Ver, 
d.DeviceName, 
MAX(t.CreatedOn) AS LastUpload, 
MAX(t.EventTimeStamp) AS LastRecord

FROM (
    SELECT de.DeviceId, 
    de.Firmware, 
    de.CreatedOn, 
    de.EventTimeStamp, 
    ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY Firmware DESC) AS RN 
    FROM AuroraCore.dbo.DeviceEvent AS de
) AS t

RIGHT JOIN AuroraCore.dbo.Device AS d ON d.Id = t.DeviceId

WHERE RN = 1

GROUP BY d.serial, t.Firmware, d.DeviceName

Now the problem is that with WHERE RN = 1 causes none of the NULL values to get returned whereas I need those to be displayed. Removing the WHERE gets me the same exact results list as before. Where can I move/adapt the WHERE RN = 1 clause so that NULL values are also returned?

I'm missing the "Description" column in sample data. Isn't problem there? — TcKs
– TcKs, Commented Apr 21, 2017 at 6:03
My apologies, I removed it from the results as they were all blank and not effecting my results anyway. The main post is updated. — Trevor
– Trevor, Commented Apr 21, 2017 at 11:24
Updated the main post to clarify my question as it wasn't concise before. — Trevor
– Trevor, Commented Apr 24, 2017 at 22:08

Trevor · Accepted Answer · 2017-04-25 17:05:41Z

I got it working through the below query:

SELECT d.Serial, 
t.Firmware AS FW_Ver, 
d.DeviceName, 
MAX(t.CreatedOn) AS Upload, 
MAX(t.EventTimeStamp) AS Record

FROM (
    SELECT de.DeviceId, 
    de.Firmware, 
    de.CreatedOn, 
    de.EventTimeStamp, 
    ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY Firmware DESC) AS RN 
    FROM AuroraCore.dbo.DeviceEvent AS de
) AS t

RIGHT JOIN AuroraCore.dbo.Device AS d ON d.Id = t.DeviceId

WHERE RN = 1 OR t.CreatedOn is NULL

GROUP BY d.serial, t.Firmware, d.DeviceName

All I added in to my previous edit was OR t.CreatedOn is NULL which included the NULL values as I wanted. Using the ROW_NUMBER() OVER PARTITION in conjunction with the WHERE RN = 1 is what got rid of the older firmware versions being displayed as the subquery would only return the highest version of firmware (as dictated by the PARTITION BY DeviceId ORDER BY Firmware DESC) for a given device and subsequently only the most recent datetimes for the uploaded and recorded dates.

Collectives™ on Stack Overflow

Selecting distinct values from multiple columns with multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related