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 |
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)
I need to get each date for the maximum value of the Count column.
In SQL-Server 2012; You can do it using correlated subquery as in this demo
SELECT id, date, count
FROM table_name t1
WHERE count = (
SELECT MAX(count)
FROM table_name t2
WHERE t1.date = t2.date
)
order by id;
Output :
id date count
2 2022-01-01 3
4 2022-01-02 7
Here is the SQL sample in a very simple way, you don't need to use DISTINCT instead you should use GROUP BY then add some COUNT.
MySQL
SELECT DATE(date) AS day, COUNT(*) AS count
FROM table_name
GROUP BY day;
Here is the result SQL imgage I hope it helps!
SQL-server-2012
CREATE TABLE table_name (
id INT PRIMARY KEY,
date DATE NOT NULL,
count INT NOT NULL
);
INSERT INTO table_name (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 CONVERT(date, date) AS day, COUNT(*) AS count
FROM table_name
GROUP BY CONVERT(date, date);
Output
