How to display last record per parameter?

Question

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.

Answer 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