CODEWITHSUNDEEP
mysqlsql
Kern Elliott
Kern Elliott

Reputation: 1659

Rank for most occuring values SQL

I have an SQL column with values in a column and I would like to know which values occur the most in a ranked format. For example if I had data in a table called List and a column had values as seen below:

COLUMN
one
five
five
five
three
two
eight
nine
two
one
two
two

The sql should return the top 3 values which are Two, Five and One. How can this be done in SQL. Please note I am using MYSQL.

Also if each Column value had a time stamp on it is it possible to find out which value occurred the most during a week without having to manually enter the start and end of the week?

Upvotes: 1

Views: 580

Answers (4)

Behnam
Behnam

Reputation: 1063

consider your table has 2 coloumns [Col1] and [Time]:

select col1 , COUNT(col1) as QTY from TBL1
where [time] between  CURRENT_TIMESTAMP and CURRENT_TIMESTAMP-7
group by col1 
order by QTY desc

Upvotes: 0

T I
T I

Reputation: 9933

try

set @l:=0, @n:=0, @w:=current_timestamp;

select w, c, n, l
from (
    select 
        w
        , c
        , n
        , @l:=case when @n=n and @w=w then @l
                   when @n<>n and @w=w then @l+1
                   else 1 end l
        , @n:=n
        , @w:=w
    from (
      select 
          col c
          , count(1) n
          , adddate(datecol, INTERVAL 1-DAYOFWEEK(datecol) DAY) w
      from list
      group by col, adddate(datecol, INTERVAL 1-DAYOFWEEK(datecol) DAY)
      order by adddate(datecol, INTERVAL 1-DAYOFWEEK(datecol) DAY), count(1) desc
    ) s
) t
where l<=3
order by w asc, n desc;

demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269723

To get the three most common in MySQL:

select col
from t
group by col
order by count(*) desc
limit 3;

If you want to get the top 3 counts -- even if there are duplicates -- then the query is a bit more cumbersome. Here is one way:

select c.col
from (select col, count(*) as cnt
      from t
      group by col
      order by cnt desc
      limit 3
     ) cols join
     (select col, count(*) as cnt
      from t
      group by col
     ) c
     on cols.cnt = c.cnt;

Finally, I know of no way of getting records for a particular week without specifying the dates to define the week.

Upvotes: 0

roman
roman

Reputation: 117380

while T I provide an answer, I should warn you that if you want consistent results, you have to specify other columns in order by. Suppose you have your table like:

('one'),
('five'),
('five'),
('five'),
('three'),
('two'),
('eight'),
('nine'),
('two'),
('one'),
('two'),
('two'),
('nine')

So you have 4 of five, 3 of two and 2 of nine and one. Which one will pop in the results? I think that you should specify it by yourself.
If you want to get all rows there count is equal to top 3 counts, in SQL Server and PostgreSQL you could do this:

;with cte as (
    select
       col,
       count(*) as cnt,
       dense_rank() over(order by count(*) desc) as rnk
    from list
    group by col
)
select col, cnt
from cte
where rnk <= 3

=> sql fiddle example

Upvotes: 1

Related Questions