I found this link to solve the most frequent value Find most frequent value in SQL column

SELECT top1 
    `value`,
    COUNT(`value`) AS `value_occurrence` 
FROM     
    `my_table`
GROUP BY 
    `value`
ORDER BY 
    `value_occurrence` DESC;

The top 1 is a problem, and I need to get the most frequent value by date

Columns

Date | WorkedHours 

Data

2022-01 | 179
2022-01 | 154
2022-01 | 179

2022-02 | 179
2022-02 | 154
2022-02 | 154

Liked result

Date | WorkedHours 
2022-01 | 179
2022-02 | 154

CodePudding user response：

with ct as(
select dat_col
       , WorkedHours
       , count(*) over(partition by dat_col, WorkedHours) rn
from test)
select * 
from ct
where rn = (select max(rn) from ct)
group by dat_col
       , WorkedHours
       , rn;

Here is a demo

I believe I have understood your request wrong. Here is a new solution:

with ct as(
select dat_col
       , WorkedHours
       , count(*) over(partition by dat_col, WorkedHours) rn
from test)
select ct.dat_col
       , ct.WorkedHours
       , ct.rn
from ct
join (select max(rn) rn, dat_col from ct group by dat_col ) ct1
on ct.dat_col = ct1.dat_col and ct.rn = ct1.rn
group by ct.dat_col
       , ct.WorkedHours
       , ct.rn;

Here is a new demo

CodePudding user response：

DECLARE @temp TABLE (datum DATE, timmar INT)
INSERT INTO @temp
SELECT '2022-01-01',179 UNION ALL
SELECT '2022-01-01',179 UNION ALL
SELECT '2022-01-01',154 UNION ALL
SELECT '2022-01-02',154 UNION ALL
SELECT '2022-01-02',154 UNION ALL
SELECT '2022-01-02',179


SELECT datum,
       timmar,
       CAST(COUNT(timmar) AS INT) 'antal'
FROM @temp
GROUP BY datum, timmar
ORDER BY datum;


DECLARE @temp2 TABLE (datum DATE, timmar INT, antal int)
INSERT INTO @temp2
SELECT datum,
       timmar,
       CAST(COUNT(timmar) AS INT) 'antal'
FROM @temp
GROUP BY datum,
         timmar


SELECT DISTINCT t.datum,t.timmar
FROM    (
        SELECT  DISTINCT datum,antal
        FROM    @temp2
        ) mo
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    @temp2 mi
        WHERE   mi.datum = mo.datum
        ORDER BY antal DESC
        ) t