Use GROUP BY and return more than 2 columns

Question

I am trying to find the latest unit_status, and the timestamp of that status (cdts), for every unid I have in a table.

This is what I have come up with so far:

with cte as 
(
    select * 
    from un_hi 
    where ag_id = 'bfd' and cdts > '201708' and
    unit_status != 'uc'
)
select distinct(unid), max(cdts2) Last_GPS_Report_Date
from cte
where unid in ('C06','C07','D01','D03','D04','D06')
group by unid
order by unid

It correctly returns every unid and the time of their latest status (max(cdts)), but I want to see what the unit_status was.

I have tried taking this query and joining it to the UN_HI table (which is where this data is coming from), but I couldn't get that to work. Any other suggestions?

Answer 1

You can use ROW_NUMBER:

with cte as 
(
    select  *,
            RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
    from un_hi 
    where ag_id = 'bfd' 
    and cdts > '201708' 
    and unit_status != 'uc'
)
select *
from cte
where RN = 1;

Answer 2

For any people using SQL Server 2000 or older, here's a way of doing it without the ROW_NUMBER Window Function:

SELECT u.*
FROM un_hi u
INNER JOIN (
    SELECT unid, MAX(cdts2) AS MaxDate
    FROM un_hi
    WHERE ag_id = 'bfd'
        AND cdts > '201708'
        AND unit_status != 'uc'
    GROUP BY unid
    ) MaxDates
        ON MaxDates.unid = u.unid
        AND MaxDates.MaxDate = u.cdts2
ORDER BY unid

Answer 3

Another option is to use GROUP BY with concatenation. The idea is that you take the aggregate of the column as usual but append other columns to go along for the ride. As long as you're careful with data type conversions you can take the minimum of a string that concatenates together all relevant columns and piece them out later. There's a discussion of this technique here.

I'm going to make some guesses about data types for your table and I'll insert some data into it:

CREATE TABLE #uh_hi (
unid BIGINT NOT NULL,
cdts DATETIME NOT NULL,
unit_stats VARCHAR(30) NOT NULL
);

INSERT INTO #uh_hi WITH (TABLOCK)
SELECT 
t.RN / 3
, DATEADD(HOUR, t.RN, '20170101')
, REPLICATE(CHAR(64 + t.RN % 26), 20)
FROM
(
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

Here's one way to return the result set that you're looking for:

SELECT unid
, CAST(SUBSTRING(magic_column, 1, 23) AS DATETIME) cdts 
, SUBSTRING(magic_column, 24, 8000) unit_stats
FROM
(
    SELECT unid
    , MIN(
        CONVERT(CHAR(23), cdts, 126)
        + unit_stats
    ) magic_column
    FROM #uh_hi
    GROUP BY unid
) t;

Note how magic_column has the aggregate column first and the other column is appended to it. Outside of the derived table I use SUBSTRING to split the columns back out. Here's a sample of the results:

╔═══════╦═════════════════════════╦══════════════════════╗
║ unid  ║          cdts           ║      unit_stats      ║
╠═══════╬═════════════════════════╬══════════════════════╣
║     0 ║ 2017-01-01 01:00:00.000 ║ AAAAAAAAAAAAAAAAAAAA ║
║ 28387 ║ 2026-09-19 09:00:00.000 ║ KKKKKKKKKKKKKKKKKKKK ║
║  5834 ║ 2018-12-31 06:00:00.000 ║ DDDDDDDDDDDDDDDDDDDD ║
║ 11407 ║ 2020-11-26 21:00:00.000 ║ EEEEEEEEEEEEEEEEEEEE ║
║ 22814 ║ 2024-10-22 18:00:00.000 ║ JJJJJJJJJJJJJJJJJJJJ ║
╚═══════╩═════════════════════════╩══════════════════════╝

This looks complicated, and it is. One advantage to this approach is that SQL Server will be able to create more accurate cardinality estimates on the result set since it knows that the number of rows will be equal to the number of distinct unid values in the table. For this example, the cardinality estimate is perfect:

If this query is part of a more complex query then the optimizer may be able to generate a better performing plan. The cardinality estimate for queries that filter against window functions, such as the ROW_NUMBER() solution, are almost always inaccurate:

The optimizer has poor modeling support for such queries. Again, this only might matter if your query is part of some larger, more complex query. If all that you need is the result set that you asked for in the question then you should use the simplest solution or the solution that you're most comfortable with.