CODEWITHSUNDEEP
sql
Alan Chuang
Alan Chuang

Reputation: 83

Find the most frequent data every year in SQL

TableA :

Years Data
2000  A
2000  B
2000  C
2000  C
2000  D
2001  A
2001  B
2001  B
2002  B
2002  D
2002  D

I want to output:

Years Data
2000  C
2001  B
2002  D

My solution:

SELECT DISTINCT Years, Data
FROM
(
   SELECT Years, Data, COUNT(*) AS _count
   FROM TableA
   GROUP BY Years, Data
) a1
ORDER BY Years, _count DESC

But it have a problem:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

How do I correct my SQL code?

Upvotes: 0

Views: 58

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

Your error is "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

This means you have put something in the ORDER BY that is not in the SELECT. In this case, _count DESC is not in the SELECT statement

SELECT DISTINCT Years, Data, _count DESC
FROM
(
   SELECT Years, Data, COUNT(*) AS _count
   FROM TableA
   GROUP BY Years, Data
) a1
ORDER BY Years, _count DESC

Upvotes: 0

D. Mika
D. Mika

Reputation: 2808

If you're using oracle, you can use the function STATS_MODE

  select years, stats_mode(data)
    from tablet
group by years;

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions154.htm

Upvotes: 0

shA.t
shA.t

Reputation: 16968

Try this:

select t.Years, t.[Data]
from (
    select *, count(*) cnt
    from TableA
    group by years, [Data]
    ) t
left join (
    select Years, max(cnt) maxCnt
    from (
        select *, count(*) cnt
        from TableA
        group by years, [Data]
    ) t
    group by Years
    ) tt on t.Years = tt.Years   -- tt is a view that gives you max count of each year
where t.cnt = tt.maxCnt          -- you need `years`, `[Data]` that its count is max count
order by years;

SQL Fiddle Demo

Another way is to use rank() in a DBMS that supports it:

;with t as (
    select *, count(*) cnt
    from TableA
    group by years, [Data]
), tt as (
    select *, rank() over (partition by years order by cnt desc) rn
    from t
)
select years, [Data]
from tt
where rn = 1
order by years;

SQL Fiddle Demo

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

Assuming your database supports row_number(), you can do it like this:

SELECT Years, Data
FROM 
(
    SELECT  Years,
            Data,
            ROW_NUMBER() OVER(PARTITION BY Years ORDER BY count(*) DESC) rn

    FROM TableA
    GROUP BY Years, Data
) x
WHERE rn = 1
ORDER BY Years, Data

See a live demo on rextester.

Upvotes: 1

Related Questions