Rank for most occuring values SQL

Question

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?

Take a look at this answer: http://stackoverflow.com/questions/11255938/efficient-use-of-sql-group-by-sum-count — iCantSeeSharp, Aug 11 '13 at 11:35

T I · Answer 1 · 2013-08-11T13:55:40.183

1

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

edited Aug 11 '13 at 13:55

answered Aug 11 '13 at 11:44

T I

9,785
4
29
51

If u had another column which stored a time stamp can I programatically find which value had the most occurrences for a week? – Kern Elliott Aug 11 '13 at 12:06
How come we are not using date end? Also would this consistently check every new week in the the month??? – Kern Elliott Aug 11 '13 at 12:19

score 1 · Answer 2 · answered Aug 11 '13 at 12:07

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

If u had another column which stored a time stamp can I programatically find which value had the most occurrences for a week? — Kern Elliott, Aug 11 '13 at 12:12

score 0 · Answer 3 · answered Aug 11 '13 at 12:59

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.

score 0 · Answer 4 · answered Nov 03 '14 at 17:50

0

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

answered Nov 03 '14 at 17:50

Behnam

1,039
2
14
39

Rank for most occuring values SQL

4 Answers4