CODEWITHSUNDEEP
sql-servermaxaggregate
Willy
Willy

Reputation: 10638

Get last row by datetime in SQL Server

I have below SQL table:

Id | Code | DateTime1                 | DateTime2
1    3AA2   2017-02-01 14:23:00.000     2017-02-01 20:00:00.000
2    E323   2017-02-12 17:34:34.032     2017-02-12 18:34:34.032
3    DFG3   2017-03-08 09:20:10.032     2017-03-08 12:30:10.032
4    LKF0   2017-04-24 11:14:00.000     2017-04-24 13:40:00.000
5    DFG3   2017-04-20 13:34:42.132     2017-04-20 15:12:12.132
6    DFG3   2017-04-20 13:34:42.132     NULL

Id is an auto numeric field. Code is string and Datetime1 and DateTime2 are datetime type. Also DateTime1 cannot be null but datetime2 can be.

I would like to obtain the last row by datetime1 (MAX datetime1, most recent one) that match a concrete code and it has datetime2 set to NULL.

For example, taken into account above table, for code DFG3 I would like to obtain row with Id=6, its max date for datetime1, that is "2017-04-20 13:34:42.132"

But now imagine the following case:

Id | Code | DateTime1                | DateTime2
1    3AA2   2017-02-01 14:23:00.000    2017-02-01 20:00:00.000
2    E323   2017-02-12 17:34:34.032    2017-02-12 18:34:34.032
3    DFG3   2017-03-08 09:20:10.032    2017-03-08 12:30:10.032
4    LKF0   2017-04-24 11:14:00.000    2017-04-24 13:40:00.000
5    DFG3   2017-04-20 13:34:42.132    NULL
6    DFG3   2017-05-02 16:34:34.032    2017-05-02 21:00:00.032

Again, taken into account above table, I would like to obtain the same, that is, the last row by datetime1 (Max datetime1, most recent one) that match a concrete code and it has datetime2 set to NULL.

Then, in this last case for code DFG3 no rows must be return because row with Id=6 is the last by datetime1 (most recent) for code DFG3 but is not NULL.

How can I do this?

Upvotes: 0

Views: 327

Answers (2)

Ibrahim Mohsen
Ibrahim Mohsen

Reputation: 22

Can you try this query and let me know if it works for your case

Select * From [TableName] where [Code]='DFG3' and [datetime2] is null and [datetime1] = (select max([datetime1]) from [TableName] where [Code]='DFG3')

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

This bring you all the latest code on your table, then you select only the one with datetime2 is null

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Code 
                                 ORDER BY DateTime1 Desc) as rn
    FROM yourTable
    ) as T
WHERE rn = 1   -- The row with latest date for each code will have 1
  and dateTime2 IS NULL
  and code = 'DFG3' -- OPTIONAL

Upvotes: 1

Related Questions