How to select the latest value by date from a group of rows?

Question

SQL Server

There is a table: client id, date of the beginning of the month, date of the middle of the month, balance of the beginning of the month, balance of the middle of the month.
id are not unique, those 1 client occurs several times.

id, balance_date_start, balance_start, balance_date_mid, balance_mid

You need to write a query that returns the balance of the beginning and middle of the month of each unique client with the most recent date.

Wrote like this:

select distinct id, BALANCE_DATE_start, BALANCE_start, BALANCE_DATE_mid, BALANCE_DATE_mid  from table
where BALANCE_DATE_start in (
select max (BALANCE_DATE_start) from table)
group by ID

However, if there are records with the same start date in the table, it gives erroneous records that are not the last for the client.



an example of a database like this, dates are those dates on which check-in occurs, but each client is checked only 2 times a month, those 2 records with the same id and BALANCE_DATE_start cannot be

id            BALANCE_DATE_start   BALANCE_start BALANCE_DATE_mid   BALANCE_DATE_mid
676         2021-10-02            255               2021-10-17                 255
155         2021-10-02            222               2021-10-15                 197
676         2021-09-01            211               2021-09-14                 333    
155         2021-11-01            159               2021-11-15                 104

the table continues further and there are several million values ​​in it, a piece for an example

.

Answer 1

see ROW_NUMBER

SELECT
    id,
    balance_date_start,
    balance_start,
    balance_date_mid,
    balance_mid
  FROM (
    SELECT
        cb.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date_start DESC) r
      FROM client_balance cb
    ) t
  WHERE r = 1
;

see example on db<>fiddle

Answer 2

Maybe that will fit?

CREATE TABLE client_balance
(
    id SMALLINT AUTO_INCREMENT,
    id_client INTEGER,
    balance_date_start DATETIME,
    balance_start INTEGER,
    balance_date_mid DATETIME,
    balance_mid INTEGER,
    PRIMARY KEY (id)
);

SELECT cb.*,
       DATEDIFF(cb.balance_date_mid, cb.balance_date_start) AS diff
FROM client_balance AS cb
GROUP BY cb.id_client
HAVING MAX(diff);