CODEWITHSUNDEEP
sqlsql-servert-sql
lechnerio
lechnerio

Reputation: 980

Get last two max dates from one row

I have a table I'd like to get desired data from.

The table itself looks like this:

+--------+--------+------------+-------------------------------+
| persnr | client | date       | other columns with other data |
+--------+--------+------------+-------------------------------+
| 123    | 1      | 01.01.2021 |                               |
| 123    | 1      | 02.01.2021 |                               |
| 123    | 1      | 03.01.2021 |                               |
| 999    | 1      | 01.01.2021 |                               |
| 999    | 1      | 02.01.2021 |                               |
| 999    | 1      | 03.01.2021 |                               |
| 123    | 9      | 02.01.2021 |                               |
| 123    | 9      | 10.01.2021 |                               |

I'd like to get the two latest (newest) dates that are not the same, per person and client and would therefore need

+--------+--------+------------+-------------------------------+
| persnr | client | date       | other columns with other data |
+--------+--------+------------+-------------------------------+
| 123    | 1      | 02.01.2021 |                               |
| 123    | 1      | 03.01.2021 |                               |
| 999    | 1      | 02.01.2021 |                               |
| 999    | 1      | 03.01.2021 |                               |
| 123    | 9      | 02.01.2021 |                               |
| 123    | 9      | 10.01.2021 |                               |

a person with the same number but a different client can be seen as an completely different person.

I tried getting getting the latest date first using

select distinct persnr, client, max(date) as date
from app_hours
group by persnr, client

and for one persnr and client to get the two latest dates I'm using

select distinct top 2 date
from app_hours
where persnr = '123' and  client = '1'
order by date

I'm struggling with combining them. I'd be thankful for any hints and tips.

Upvotes: 1

Views: 101

Answers (1)

Dale K
Dale K

Reputation: 27462

Generate a row number using your criteria, and then filter out all but the first 2 rows e.g.

with cte as (
    select *
        -- change to date desc for the most recent as your results show
        , row_number() over (partition by persnr, client order by date) RowNumber
    from app_hours
)
select *
from cte
where RowNumber < 3
order by client, persnr, date;

Upvotes: 3

Related Questions