Select distinct rows with max count another row

Question

help please. I need to get each date the maximum value of the Count column.

| id | date       | count |
| 1  | 2022-01-01 | 2     |
| 2  | 2022-01-01 | 3     |
| 3  | 2022-01-02 | 4     |
| 4  | 2022-01-02 | 7     |

expected result:

| id| date        | count |
|2  | 2022-01-01  |3      |
|4  | 2022-01-02  |7      |

Answer 1

You can use ROW_NUMBER to order the rows in each group (in your case one group is defined by unique date) starting with the row with highest count. Then, we are using the TOP 1 WITH TIES to get only rows with ROW_NUMBER = 1:

DROP TABLE IF EXISTS #tempTable;

CREATE TABLE #tempTable
(
    id INT,
    date DATE,
    count INT
);

INSERT INTO #tempTable (id, date, count)
VALUES
    (1, '2022-01-01', 2),
    (2, '2022-01-01', 3),
    (3, '2022-01-02', 4),
    (4, '2022-01-02', 7);

SELECT TOP 1 WITH TIES *
FROM #tempTable
ORDER BY ROW_NUMBER () OVER (PARTITION BY date ORDER BY count DESC)