Select all entries that have the same Type as the entry with the largest Date in SQL?

Question

How do I select all entries that have the same Type as the entry with the largest Date?

I'm using SQL Server.

My table:

+----+------+-------------------------+
| id | Type |          Date           |
+----+------+-------------------------+
|  1 | xxx  | 2020-02-25 09:11:53.000 |
|  2 | yyy  | 2020-02-25 08:30:35.000 |
|  3 | xxx  | 2020-02-25 07:48:17.000 |
|  4 | xxx  | 2020-02-25 09:04:25.000 |
|  5 | yyy  | 2020-02-25 07:59:03.000 |

The result should be:

+----+------+-------------------------+
| id | Type |          Date           |
+----+------+-------------------------+
|  1 | xxx  | 2020-02-25 09:11:53.000 |
|  3 | xxx  | 2020-02-25 07:48:17.000 |
|  4 | xxx  | 2020-02-25 09:04:25.000 |
+----+------+-------------------------+

Because id =1 is the Type with the max Date.

I play around with this solution https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user/2411763#2411763 but I didn't get it work :( — InFlames82, Feb 25 '20 at 10:59

Gordon Linoff · Answer 1 · 2020-02-25T11:05:42.283

A correlated subquery is often the most efficient method with the right index:

select t.*
from t
where t.type = (select top (1) t2.type
                from t t2
                order by t2.date desc
               );

The best indexes are (date desc, type) and (type).

You can also do this with window functions:

select t.*
from (select t.*,
             first_value(type) over (order by date desc) as last_type
      from t
     ) t
where type = last_type;

Thom A · Answer 2 · 2020-02-25T11:11:43.460

Rather than a Self Join, you could use LAST_VALUE in a CTE and then add that to the WHERE:

WITH CTE AS(
    SELECT V.ID,
           V.[Type],
           V.[Date],
           LAST_VALUE(V.Type) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastType
    FROM (VALUES (1, 'xxx', CONVERT(datetime2(0), '2020-02-25 09:11:53.000')),
                 (2, 'yyy', CONVERT(datetime2(0), '2020-02-25 08:30:35.000')),
                 (3, 'xxx', CONVERT(datetime2(0), '2020-02-25 07:48:17.000')),
                 (4, 'xxx', CONVERT(datetime2(0), '2020-02-25 09:04:25.000')),
                 (5, 'yyy', CONVERT(datetime2(0), '2020-02-25 07:59:03.000'))) V (ID, [Type], [Date]))
SELECT CTE.ID,
       CTE.[Type],
       CTE.[Date]
FROM CTE
WHERE [Type] = LastType;

DB<>Fiddle

score 0 · Accepted Answer · answered Feb 25 '20 at 11:11

0

You can use exists with correlated sub-query :

select t.*
from table t
where exists (select 1 from table t1 where t1.type = t.type and t1.id <> t.id) and
      t.type = (select top (1) t1.type from table t1 order by t1.date desc);

answered Feb 25 '20 at 11:11

Yogesh Sharma

49,870
5
26
52

score -1 · Answer 4 · answered Feb 25 '20 at 11:01

Try this:

Declare @t table (id int , types nvarchar(100),dates datetime)

insert into @t values (1,'xxx','2020-02-25 09:11:53.000')
insert into @t values (2,'yyy','2020-02-25 08:30:35.000')
insert into @t values (3,'xxx','2020-02-25 07:48:17.000')
insert into @t values (4,'xxx','2020-02-25 09:04:25.000')
insert into @t values (5,'yyy','2020-02-25 07:59:03.000')


Declare @max nvarchar(100) = (select t.types from (
select top 1 max(dates) as t,types  from @t group by types
) t)

select * from @t 
where types = @max

Output:

id  types   dates
1   xxx 2020-02-25 09:11:53.000
3   xxx 2020-02-25 07:48:17.000
4   xxx 2020-02-25 09:04:25.000

Select all entries that have the same Type as the entry with the largest Date in SQL?

4 Answers4