5

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?

  • 1
    add example data. – Daniel Björk Aug 14 '17 at 19:24
  • 3
    distinct is not a function; it applies to the entire select list. – mustaccio Aug 14 '17 at 19:48
  • 2
    What @mustaccio said above ^^. Also it's useless is this query. The GROUP BY makes sure that every row in the result set has a distinct unid. – ypercubeᵀᴹ Aug 14 '17 at 21:00
10

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;
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
  • The ROW_NUMBER method, on the other hand, is a way of doing it without any joins. – Andriy M Aug 16 '17 at 22:26
  • Very true, I also prefer using Window Functions where I can. I went down this not-as-good path by mistakenly thinking SQL 2008 didn't support ROW_NUMBER, but it does. I might leave my answer for those unlucky souls working with SQL 2005 or older versions... – Oreo Aug 17 '17 at 8:20
  • SQL Server 2005 supports ROW_NUMBER too, so 2000 or older, but yes, there might still be some among us working with ancient systems. – Andriy M Aug 17 '17 at 9:11
  • Wow, we got ROW_NUMBER much earlier than I thought... Thanks for the correction Andriy! – Oreo Aug 17 '17 at 9:51
1

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:

perfect estimate

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:

bad estimate

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.