SQL query for max date value

Question

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

how do you want to handle cases where there is a tie, e.g. PK id rows 15-18 (assuming PK id rows 19 and 20 do not exist)? — mikey
– mikey, Commented Dec 29, 2015 at 14:05
If the newest record's type is 400, do you want that record to be returned, or nothing, so, with your example, for ID 16, do you want to get back the row with PK 19/20 or nothing? — DaveyDaveDave
– DaveyDaveDave, Commented Dec 29, 2015 at 14:08
Adding some expected output would probably help others to understand what it is that you're trying to achieve. — Tom H
– Tom H, Commented Dec 29, 2015 at 14:13
This is a sql server database. Database name is adminstudio. If the newest record is 400 i do not want it to return. What i would like to see returned is just PK and newest date it was at 401 unless its newest date was 400. Hope that helps. — Michael Miller
– Michael Miller, Commented Dec 30, 2015 at 15:12

Andomar · Accepted Answer · 2015-12-30 19:20:38Z

1

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.

edited Dec 30, 2015 at 19:20

answered Dec 29, 2015 at 14:05

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mikey Over a year ago

Will the subquery return multiple rows in some cases?

Andomar Over a year ago

@mikey: Good point, I'll add an aggregate. Would be a better query with window functions for sure.

Michael Miller Over a year ago

That seems to work for the most part. I am randomly getting items that do not fall out of the query when set back to 400. The database we are using is a sql server database. Thanks for your help.

Andomar Over a year ago

Can you provide an example where the query doesn't work as you expect? Perhaps using sqlfiddle?

Michael Miller Over a year ago

TYPE date 401 12/15/15 18:34 401 12/15/15 18:34 400 12/29/15 13:04 400 12/29/15 13:04 401 12/29/15 13:04 401 12/29/15 13:04 400 12/29/15 13:42 400 12/29/15 13:42 401 12/29/15 15:04 401 12/29/15 15:04 400 12/30/15 12:32 400 12/30/15 12:32 306 12/30/15 12:34 90 12/30/15 12:47

|

Collectives™ on Stack Overflow

SQL query for max date value

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related