T-SQL get the last date time record

Question

My table looks like this:

+---------+------------------------+-------+---------+---------+
|channel  |date                    |code   |comment  |order_id |
+---------+------------------------+-------+---------+---------+
|1        |2017-10-27 12:04:45.397 |2      |comm1    |1        |
|1        |2017-10-27 12:14:20.997 |1      |comm2    |1        |
|2        |2017-10-27 12:20:59.407 |3      |comm3    |1        |
|2        |2017-10-27 13:14:20.997 |1      |comm4    |1        |
|3        |2017-10-27 12:20:59.407 |2      |comm5    |1        |
|3        |2017-10-27 14:20:59.407 |1      |comm6    |1        |
+---------+------------------------+-------+---------+---------+

And I expect result like this:

+---------+------------------------+-------+---------+
|channel  |date                    |code   |comment  |
+---------+------------------------+-------+---------+
|1        |2017-10-27 12:14:20.997 |1      |comm2    |
|2        |2017-10-27 13:14:20.997 |1      |comm4    |
|3        |2017-10-27 14:20:59.407 |1      |comm6    |
+---------+------------------------+-------+---------+

Always 1 record with order_id = x and max date for each channel. Total number of channels is constant. My query works but I'm worried about performance as the table grows. Doing three almost identical queries doesn't seem smart.

select
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 1 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel1
union
select 
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 2 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel2
union
select 
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 3 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel3

How can I improve this?

Possible duplicate of [T-SQL: Selecting Column Based on MAX(Other Column)](https://stackoverflow.com/questions/3680254/t-sql-selecting-column-based-on-maxother-column) — Clockwork-Muse, Oct 27 '17 at 17:59

score 3 · Accepted Answer · answered Oct 27 '17 at 17:51

3

Another option is using the WITH TIES clause. No sub-query or extra field.

Select top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By channel order by date desc)

answered Oct 27 '17 at 17:51

John Cappelletti

79,615
7
44
66

Didn't know you could do this. Brilliant, thanks for sharing. – Simon Oct 27 '17 at 17:53
@Simon I learn something every day here. That's the fun part :) – John Cappelletti Oct 27 '17 at 17:54
Very elegant solution! I'm going to use this. Thank you Sir. :) – adriangutowski Oct 27 '17 at 17:57
@gutowskiap Happy it helps – John Cappelletti Oct 27 '17 at 18:06

score 2 · Answer 2 · answered Oct 27 '17 at 17:50

Try using the ROW_NUMBER() function and a derived table. It will save you a lot of headaches. Try:

select channel
       ,date
       ,code
       ,comment
from
(select *
       ,row_number() over(partition by channel order by code asc) rn --probably don't need asc since it is ascending by default
from mytable) t
where t.rn = 1

score 0 · Answer 3 · answered Oct 27 '17 at 17:53

0

Assuming you want the latest row for each channel, this would work.

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY s.channel ORDER BY [date] DESC) AS rn,
        *
    FROM [status] AS s
) AS t
WHERE t.rn = 1

answered Oct 27 '17 at 17:53

Jason Pease

62
7

T-SQL get the last date time record

3 Answers3