How to select latest entry by name and date?

Question

SQL

There is a table
id | name | salary | period start | period end
1 | Egor | 344 | 2020-08-01 | 2020-08-31
2 | Arthur | “567” | 2020-08-01 | 2020-08-31
3 | Egor | 768 | 2020-08-01 | 2020-08-31
4 | Arthur | “356” | 2020-08-01 | 2020-08-31
5 | Dima | 864 | 2020-08-01 | 2020-08-31
6 | Dima | 357 | 2020-08-01 | 2020-08-31
7 | Dima | 865 | 2020-08-01 | 2020-08-31

Expected Result :
Get latest records by “id” for each name Egor, Artur, Dima and date from 2020-08-01 to 2020-08-31

3 | Egor | 768 | 2020-08-01 | 2020-08-31
4 | Arthur | “356” | 2020-08-01 | 2020-08-31
7 | Dima | 865 | 2020-08-01 | 2020-08-31

Answer 1

You can find the maximum identifiers for each name in a subquery, and then simply select the data by them:

SELECT *
FROM salary 
WHERE id IN (SELECT MAX(id) FROM salary GROUP BY name);

Answer 2

SELECT *
FROM
  (SELECT id,
          name,
          salary,
          period-start,
          period-end,
          row_number() OVER (PARTITION BY name ORDER BY id DESC) AS rn
   FROM your_table
   WHERE period-start = ...
     AND period-end = ...)
WHERE rn = 1

Only instead of * you need to list the required fields in the actual request.