SQL Server – Get Last Two IDs from Rows for the Last Two Days

greatest-n-per-groupsql server

I have a table that looks like this:

    +------------+------------+--------+----------+--------------+
    | terminalid |    date    | toolid | toolname | workcenterid |
    +------------+------------+--------+----------+--------------+
    |         17 | 2015-12-17 |    132 | RE8      |            7 |
    |         17 | 2015-12-17 |    140 | RE77     |           10 |
    |         17 | 2015-12-17 |    141 | RE0      |            1 |
    |         17 | 2015-12-17 |    153 | RTR      |           13 |
    |         18 | 2015-12-18 |     29 | C47      |           12 |
    |         18 | 2015-12-18 |     53 | C45      |           10 |
    |         18 | 2015-12-18 |     58 | TXR      |            9 |
    |         18 | 2015-12-18 |     61 | BMS      |            6 |
    |         19 | 2015-12-21 |    128 | REV      |            3 |
    |         19 | 2015-12-21 |    135 | REZS     |           12 |
    |         19 | 2015-12-21 |    154 | REP      |            1 |
    |         20 | 2015-12-22 |     21 | RR14     |            9 |
    |         20 | 2015-12-22 |     69 | REX      |            1 |
    |         20 | 2015-12-22 |     71 | REA      |            4 |
    |         20 | 2015-12-22 |     72 | REW      |           11 |
    |         20 | 2015-12-22 |     91 | RER      |            1 |
    +------------+------------+--------+----------+--------------+

I want to get the last 2 terminalids from the rows where the date is today or yesterday.

For example, if today was 2015-12-18, the query should return me this:

    +------------+------------+--------+----------+--------------+
    | terminalid |    date    | toolid | toolname | workcenterid |
    +------------+------------+--------+----------+--------------+
    |         17 | 2015-12-17 |    132 | RE8      |            7 |
    |         17 | 2015-12-17 |    140 | RE77     |           10 |
    |         17 | 2015-12-17 |    141 | RE0      |            1 |
    |         17 | 2015-12-17 |    153 | RTR      |           13 |
    |         18 | 2015-12-18 |     29 | C47      |           12 |
    |         18 | 2015-12-18 |     53 | C45      |           10 |
    |         18 | 2015-12-18 |     58 | TXR      |            9 |
    |         18 | 2015-12-18 |     61 | BMS      |            6 |
    +------------+------------+--------+----------+--------------+

If today was 2015-12-19, then only the terminalid=18 rows should be returned. And if today was, for instance, 2015-12-20, no rows should be returned, because there is no data either for today or for yesterday.

How can I achieve that?

I'm using SQL Server.

Best Answer

You can use the rank function to get the last two terminalid's:

select terminalid, date, toolid, toolname, workcenterid
from (
    select terminalid, date, toolid, toolname, workcenterid
         , dense_rank() over ( order by terminalid desc ) as rnk
    from T
    where date <= now() -- this will be dependent of your DBMS
) as X
where rnk <= 2;

Note that date is a reserved word so it is wise to name the column otherwise. Beside that it is a date of some kind (hard to tell from the question), say transaction_date in lack of a better understanding of your domain.

Related Question