finding nth most occurring value in mysql column

0
votes

I have a table:

alphabet occurrence

a 22
b 22
c 21
d 12
a 22
g 20
h 11
c 22
b 32

how to find 3rd most occurring value in this table using SQL Command.\

I am using something like:

SELECT occurrence, COUNT(occurrence) from mytable group by occurrence order by count(occurrence) desc limit 3;

output expected:
if n = 3
c count(occurrence of c) 
c 43
2
mysqlsql
what output are you expecting? – John Ruddell
How would you handle ties? – Strawberry
added expected output, assume no ties – Bharthan
most occurring value : determined by sum of occurrences or count entries in table ? – amdixon

2 Answers

3
votes

a way to do this is to count the rows as you select them and filter off of that selection

SELECT letter, SUM(occurances) as num_occ, @A := @A + 1 as row_count
FROM occ, (select @A := 0) t
GROUP BY letter 
HAVING row_count = 3
ORDER BY num_occ DESC;

this is kind of a hacky way to do it. but since you dont have an ID in the table theres not much for an alternative

FIDDLE DEMO

EDIT: after understanding the requirement better here is another alternative.

SELECT letter, SUM(occurances) as num_occ
FROM occ
GROUP BY letter 
ORDER BY num_occ DESC
limit 2, 1; -- 2 is the 3rd row position wise starting at 0 (think of an array)

FIDDLE

0
votes

You can use offset to select nth number of row.
Here is the code for Selecting nth most occurred alphabet. 

SELECT  alphabet,
COUNT(alphabet) AS value_occurrence 
FROM     mytable
GROUP BY alphabet
ORDER BY value_occurrence DESC
LIMIT 1 OFFSET 3