How to display last record per parameter?

All we need is an easy explanation of the problem, so here it is.

I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites.

I’m having trouble figuring out how to ALSO create a column that displays the last record for each "station_id"

SQL:

SELECT xc_data1.station_id, 
xc_data1.sensorname, 
xc_sites.site_comment,
xc_sites.SITE_LONG_NAME,
xc_sites.IPADDRESS,
count(xc_data1.time_tag) as result_count,
min(xc_data1.time_tag) as start_time,
max(xc_data1.time_tag) as last_time
FROM [XC_DATA].[dbo].[xc_sites] INNER JOIN [XC_DATA].[dbo].[xc_data1] ON xc_sites.station_id = xc_data1.station_id
where time_tag > DATEADD(day, -7, GETDATE())
GROUP BY xc_data1.station_id, xc_data1.sensorname,xc_sites.site_comment, xc_sites.SITE_LONG_NAME, xc_sites.IPADDRESS
order by last_time desc

RESULT:

station_id sensorname site_comment SITE_LONG_NAME IPADDRESS result_count start_time last_time
11370 RAIN marshy Dead Marshes 10.123.192.6 2062 7/14/2022 11:00 7/21/2022 14:55
11369 RAIN sandy Hobbit Hole 10.123.192.56 2061 7/14/2022 11:00 7/21/2022 14:55

DESIRED RESULT:

station_id sensorname site_comment SITE_LONG_NAME IPADDRESS result_count Last_Record start_time last_time
11370 RAIN marshy Dead Marshes 10.123.192.6 2062 0.01 7/14/2022 11:00 7/21/2022 14:55
11369 RAIN sandy Hobbit Hole 10.123.192.56 2061 0.5 7/14/2022 11:00 7/21/2022 14:55

Edit:

I’ve worked up this to retrieve the latest value

SELECT station_id, sensorname, time_tag, orig_value
FROM   (SELECT station_id, sensorname, time_tag, orig_value,
               RANK() OVER (PARTITION BY station_id ORDER BY time_tag DESC) AS rk
        FROM   [XC_DATA].[dbo].[xc_data1]) t
WHERE  rk = 1
ORDER BY time_tag desc

but I’m not sure how to combine this into the previous query.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I don’t know exactly what you want, but I’m guessing you want to take your aggregate data and combine it with the latest data. Are you looking for something like this?

WITH AggregateData AS (
    SELECT xc_data1.station_id, 
    xc_data1.sensorname, 
    xc_sites.site_comment,
    xc_sites.SITE_LONG_NAME,
    xc_sites.IPADDRESS,
    count(xc_data1.time_tag) as result_count,
    min(xc_data1.time_tag) as start_time,
    max(xc_data1.time_tag) as last_time
    FROM [XC_DATA].[dbo].[xc_sites] INNER JOIN [XC_DATA].[dbo].[xc_data1] ON xc_sites.station_id = xc_data1.station_id
    where time_tag > DATEADD(day, -7, GETDATE())
    GROUP BY xc_data1.station_id, xc_data1.sensorname,xc_sites.site_comment, xc_sites.SITE_LONG_NAME, xc_sites.IPADDRESS
    order by last_time desc
), LatestData AS (
    SELECT station_id, sensorname, time_tag, orig_value
    FROM   (SELECT station_id, sensorname, time_tag, orig_value,
                RANK() OVER (PARTITION BY station_id ORDER BY time_tag DESC) AS rk
            FROM   [XC_DATA].[dbo].[xc_data1]) t
    WHERE  rk = 1
    ORDER BY time_tag desc
)

SELECT ad.station_id
    , ad.sensorname
    , ad.site_comment
    , ad.SITE_LONG_NAME
    , ad.IPADDRESS
    , ad.result_count
    , ld.orig_value AS Last_Record
    , ad.start_time
    , ad.last_time
FROM AggregateData ad 
    JOIN LatestData ld ON ld.station_id = ad.station_id

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply