CODEWITHSUNDEEP
sqlsql-server
bsander
bsander

Reputation: 59

Microsoft SQL Server - Getting latest record for value

I'm very not SQL savvy at all but I've been tasked with putting together a report for CIS benchmark test scores. I need to get the most recent score for each system without duplicates.

The system_identifier table houses the system info fields; I'm grabbing the value type 1426 (hostname)

The test_result table contains the score and date fields.

Here is my current query:

SELECT system_identifier.value AS [System Name], test_result.score + '%' AS [Most Recent Score], LEFT(MAX(test_result.upload_date), 10) AS [Test Date]
FROM system_identifier INNER JOIN test_result ON system_identifier.target_system_id = test_result.target_system_id
WHERE (system_identifier.type_id = 1426)
GROUP BY system_identifier.value, test_result.score
ORDER BY system_identifier.value

This is currently returning all records where the System Name + Score combination is unique.

System Name   Most Recent Score   Test Date
-----------   -----------------   ---------
system1       84                  2019-06-10
system1       87                  2019-08-24
system1       94                  2019-09-14
system2       78                  2019-07-22
system2       85                  2019-09-12
system3       65                  2019-05-23
system3       74                  2019-07-03
system3       81                  2019-08-09
system3       91                  2019-09-10

Here is what I need:

System Name   Most Recent Score   Test Date
-----------   -----------------   ---------
system1       94                  2019-09-14
system2       85                  2019-09-12
system3       91                  2019-09-10

I've tried researching this but I'm not sure how to frame my question for a search, so I thought I'd ask here since I can be more verbose with what I'm looking for.

Let me know if you need any more info.

EDIT Neeraj's answer put me on the right track, so marking as the answer.

I ended up creating a view with the initial SELECT to put everything in one table called "merged." Then, I had success with this query (with some formatting to make it pretty):

SELECT DISTINCT
UPPER(merged.value) AS Hostname,
merged.score + '%' AS Score
LEFT(merged.upload_date,10) AS [Date Uploaded],
FROM dbo.merged
INNER JOIN (SELECT merged.value AS hostname, MAX(merged.upload_date) AS Uploaded
FROM dbo.merged
GROUP BY merged.value) t2
ON merged.value = t2.hostname
AND merged.upload_date = t2.Uploaded
WHERE merged.type_id = 1426
ORDER BY hostname

Upvotes: 1

Views: 96

Answers (3)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Use correlated subquery:

select t1.SystemName, t1.MostRecentScore, t1.TestDate
from myTable t1
where t1.TestDate =
(
select max(t2.TestDate)
from myTable t2
where t2.SystemName = t1.SystemName
)

Upvotes: 2

Ilyes
Ilyes

Reputation: 14928

You could also use a subquery as

WITH T AS
(
  SELECT *
  FROM
  (
    VALUES
    ('system1', 84, '2019-06-10'),
    ('system1', 87, '2019-08-24'),
    ('system1', 94, '2019-09-14'),
    ('system2', 78, '2019-07-22'),
    ('system2', 85, '2019-09-12'),
    ('system3', 65, '2019-05-23'),
    ('system3', 74, '2019-07-03'),
    ('system3', 81, '2019-08-09'),
    ('system3', 91, '2019-09-10')
  ) T(SystemName, MostRecentScore, TestDate)
)
SELECT T.*
FROM T INNER JOIN
(
  SELECT SystemName,
         MAX(TestDate) TestDate
  FROM T
  GROUP BY SystemName
) TT ON T.SystemName = TT.SystemName 
        AND
        T.TestDate = TT.TestDate;

Online Demo

Upvotes: 0

GMB
GMB

Reputation: 222702

Use ROW_NUMBER():

SELECT System_Name,Most_Recent_Score,Test_Date
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY system_name ORDER BY test_date DESC) rn
    FROM mytable t
) x WHERE rn = 1

In the inner query, ROW_NUMBER() assigns a rank to each record within groups of records having the same system name, ordered by descending test date. Then the outer query selects the top record in each group.

Demo on DB Fiddle:

System_Name | Most_Recent_Score | Test_Date          
:---------- | ----------------: | :------------------
system1     |                94 | 14/09/2019 00:00:00
system2     |                85 | 12/09/2019 00:00:00
system3     |                91 | 10/09/2019 00:00:00

Upvotes: 1

Related Questions