SQL Server. SELECT rows by newest column values

Question

I have a table named Tbl_TickTock :

ID  |   STATUS  |   DAYTIME                 |
----|-----------|---------------------------|
1   |   unknown |   2017-03-30  13:30:30    |
1   |   other   |   2016-02-02  10:10:10    |
1   |   active  |   2015-01-01  00:00:00    |

2   |   unknown |   2017-03-30  13:30:30    |
2   |   other   |   2016-02-02  10:10:10    |
2   |   passive |   2015-01-01  00:00:00    |

3   |   other   |   2017-03-30  13:30:30    |
3   |   active  |   2016-02-02  10:10:10    |
3   |   passive |   2015-01-01  00:00:00    |

4   |   passive |   2017-03-30  13:30:30    |
4   |   other   |   2016-02-02  10:10:10    |
4   |   active  |   2015-01-01  00:00:00    |

5   |   unknown |   2017-03-30  13:30:30    |
5   |   passive |   2016-02-02  10:10:10    |
5   |   active  |   2015-01-01  00:00:00    |

6   |   active  |   2017-03-30  13:30:30    |
6   |   passive |   2016-02-02  10:10:10    |
6   |   active  |   2015-01-01  00:00:00    |

DAYTIME datatype is datetime

I need two different recorsets.

FIRST Query

Select all rows with following conditions:

There is a row where STATUS = 'passive'
There is another row (or many rows) with same ID where STATUS ='active' OR
STATUS = 'other' AND this row is newer than ID's newest row with STATUS = 'passive'

Therefore,

ID = 1 : Excluded, no 'passive'

1   |   unknown |   2017-03-30  13:30:30    |
1   |   other   |   2016-02-02  10:10:10    |
1   |   active  |   2015-01-01  00:00:00    |

ID = 4 : Excluded, 'passive' is newest

4   |   passive |   2017-03-30  13:30:30    |
4   |   other   |   2016-02-02  10:10:10    |
4   |   active  |   2015-01-01  00:00:00    |

ID = 4 : Excluded, no 'active' or 'other' which is newer than 'passive'

5   |   unknown |   2017-03-30  13:30:30    |
5   |   passive |   2016-02-02  10:10:10    |
5   |   active  |   2015-01-01  00:00:00    |

Result must be ordered by ID, DAYTIME DESC

From sample above result should be:

ID  |   STATUS  |   DAYTIME                 |
----|-----------|---------------------------|
2   |   unknown |   2017-03-30  13:30:30    |
2   |   other   |   2016-02-02  10:10:10    |
2   |   passive |   2015-01-01  00:00:00    |

3   |   other   |   2017-03-30  13:30:30    |
3   |   active  |   2016-02-02  10:10:10    |
3   |   passive |   2015-01-01  00:00:00    |

6   |   active  |   2017-03-30  13:30:30    |
6   |   passive |   2016-02-02  10:10:10    |
6   |   active  |   2015-01-01  00:00:00    |

I have an idea where to start from, but I'm pretty lost so following might be hopeless:

SELECT z1.* 
FROM Tbl_TickTock z1  
INNER JOIN 
    (SELECT MAX(daytime) as MAXdaytime )
    FROM Tbl_TickTock) z2
ON z1.ID = z2.ID
AND ( here we put conditions? : 
        active is newer than passive OR 
        other  is newer than passive  )

End Of FIRST Query

SECOND Query

Similar to FIRST but returning only one row per ID.

Must fill same conditions as in FIRST query:

There is a row where STATUS = 'passive'
There is another row (or many rows) with same ID where STATUS ='active' OR
STATUS = 'other' AND this row is newer than ID's newest row with STATUS = 'passive'

ID's row must be selected by conditions in following order

If ID has a row: STATUS = 'active' then select ID's newest row where STATUS = 'passive'
else select ID's newest STATUS = 'other' row

Result from FIRST query's conditions:

ID  |   STATUS  |   DAYTIME                 |
----|-----------|---------------------------|
2   |   unknown |   2017-03-30  13:30:30    |
2   |   other   |   2016-02-02  10:10:10    |   <--- Row to be returned
2   |   passive |   2015-01-01  00:00:00    |

3   |   other   |   2017-03-30  13:30:30    |
3   |   active  |   2016-02-02  10:10:10    |   <--- Row to be returned
3   |   passive |   2015-01-01  00:00:00    |

6   |   active  |   2017-03-30  13:30:30    |   <--- Row to be returned
6   |   passive |   2016-02-02  10:10:10    |
6   |   active  |   2015-01-01  00:00:00    |

Result must be ordered by ID, DAYTIME DESC

From sample above result should be:

ID  |   STATUS  |   DAYTIME                 |
----|-----------|---------------------------|
2   |   other   |   2016-02-02  10:10:10    |

3   |   active  |   2016-02-02  10:10:10    |

6   |   active  |   2017-03-30  13:30:30    |

End Of SECOND Query

Any help highly appreciated!

SqlZim · Accepted Answer

Assuming your 2007 dates are typos and should be 2017:

rextester demo: http://rextester.com/ADPCQ97931

First Query: Uses a common table expression to aggregate the max daytime for each id and status, and uses that with a self join in an in an exists() clause to meet all three criteria.

;with cte as (
  select 
      t.id
    , t.[status]
    , daytime = max(t.daytime)
  from t
  group by t.id, t.[status]
)
select t.id, t.[status], t.daytime
from t
where exists (
  select 1 
  from cte l 
    inner join cte r
      on l.id = r.id
    and l.daytime > r.daytime
    and r.[status] = 'passive'
    and l.[status] in ('active','other')
  where t.id = l.id 
   /* statisfies requirement of passive
    , and passive is not newest
    , and 'active' or 'other' is newer than passive */
  )
order by t.id, t.daytime desc

returns:

+----+---------+---------------------+
| id | status  |       daytime       |
+----+---------+---------------------+
|  2 | unknown | 2017-03-30 13:30:30 |
|  2 | other   | 2016-02-02 10:10:10 |
|  2 | passive | 2015-01-01 00:00:00 |
|  3 | other   | 2017-03-30 13:30:30 |
|  3 | active  | 2016-02-02 10:10:10 |
|  3 | passive | 2015-01-01 00:00:00 |
|  6 | active  | 2017-03-30 13:30:30 |
|  6 | passive | 2016-02-02 10:10:10 |
|  6 | active  | 2015-01-01 00:00:00 |
+----+---------+---------------------+

Second Query: Similar to the first, but instead of an exists() clause it uses a similar query as a derived table and returns the desired row based on the sorting requirement using top with ties with row_number().

;with cte as (
  select 
      t.id
    , t.[status]
    , daytime = max(t.daytime)
  from t
  group by t.id, t.[status]
)
select s.id, s.[status], s.daytime
from (
  select top 1 with ties 
      l.*
  from cte l 
    inner join cte r
      on l.id = r.id
    and l.daytime > r.daytime
    and r.[status] = 'passive'
    and l.[status] in ('active','other')
  order by row_number() over (
      partition by l.id
      order by case when l.[status]='active' then 0 else 1 end asc
      )
  ) s
order by s.id, s.daytime desc

returns:

+----+---------+---------------------+
| id | status  |       daytime       |
+----+---------+---------------------+
|  2 | other   | 2016-02-02 10:10:10 |
|  3 | active  | 2016-02-02 10:10:10 |
|  6 | active  | 2017-03-30 13:30:30 |
+----+---------+---------------------+

SQL Server. SELECT rows by newest column values

Answers (1)

Related Questions