CODEWITHSUNDEEP
sqlsql-server
Michael Miller
Michael Miller

Reputation: 1

SQL query for max date value

I need help writing this query to get all records where type is 401 unless the newest records type is 400. ID 16 is an example of the records that are throwing me off. Im sure this is easy and im over thinking it but I have been stuck on this for a few days now. please help.

PK  ID  Type  Changeddate
1   10  400   9/30/15 20:08
2   11  401   10/7/15 18:55
3   11  401   10/7/15 18:55
4   12  400   10/9/15 20:08
5   12  400   10/9/15 20:08
6   13  401   10/14/15 14:12
7   13  401   10/14/15 14:12
8   13  400   10/15/15 15:06
9   13  400   10/15/15 15:06
10  14  401   10/26/15 17:08
11  14  401   10/26/15 17:08
12  15  401   10/29/15 3:48
13  15  401   10/29/15 3:48
14  15  400   10/29/15 19:52
15  16  400   12/29/15 13:04
16  16  400   12/29/15 13:04
17  16  401   12/29/15 13:04
18  16  401   12/29/15 13:04
19  16  400   12/29/15 13:42
20  16  400   12/29/15 13:42

Upvotes: 0

Views: 79

Answers (1)

Andomar
Andomar

Reputation: 238086

For SQL Server, you can use the last_value window function:

select  *
from    (
        select  *
        ,       last_value(Type) over (
                    partition by ID 
                    order by Changeddate
                    rows between unbounded preceding and unbounded following)
                    as last_type
        from    YourTable yt1
        where   Type in (400, 401)
        ) sub
where   last_type <> 400

Example at SQL Fiddle.

Upvotes: 1

Related Questions