Selecting the most common value from relation - SQL statement

Question

I have a table within my database that has many records, some records share the same value for one of the columns. e.g.

|  id  |  name  |  software  |
______________________________
|  1   |  john  |  photoshop |
|  2   |  paul  |  photoshop |
|  3   |  gary  |  textmate  |
|  4   |  ade   |  fireworks |
|  5   |  fred  |  textmate  |
|  6   |  bob   |  photoshop |

I would like to return the value of the most common occurring piece of software, by using an SQL statement.

So in the example above the required SQL statement would return 'photoshop' as it occurs more than any other piece of software.

Is this possible?

Thank you for your time.

@Ronnie : which version of database are you using? MySQL? Oracle ? — Michael Mao, Apr 05 '10 at 23:46
possible duplicate of [Get most common value in SQL](http://stackoverflow.com/questions/344665/get-most-common-value-in-sql) — user2284570, May 13 '14 at 06:40

score 22 · Accepted Answer · answered Apr 05 '10 at 23:44

22

select top 1 software 
from your_table 
group by software
order by count(*) desc

answered Apr 05 '10 at 23:44

Carl Manaster

39,912
17
102
155

Thanks Carl, what does the 'top 1' part mean? thanks for your quick response. – Ronnie Apr 05 '10 at 23:48
1

@Ronnie it returns from the top of the matches from the query, which in this case are descending, the # of records indicated – msarchet Apr 05 '10 at 23:50
So, this statement will order all of the records in groups starting with the group of the most common software and the descending. But the 'top 1' selects just the value from the first group? – Ronnie Apr 05 '10 at 23:54
I have tested this statement on the table using phpmyadmin and it returns an error stating 'You have an error in your SQL syntax'? – Ronnie Apr 05 '10 at 23:58
8

+1 For MySQL, remove `top 1`, and add `limit 1` at the end, behind the `order by` – Andomar Apr 06 '10 at 00:00
@Andomar Perfect, that works a treat, thanks for your comment and Carl. – Ronnie Apr 06 '10 at 00:04

score 4 · Answer 2 · answered Apr 05 '10 at 23:49

It depends if you want to use standard SQL or vendor specific extensions (another poster has a "top N" query which is not standard). A standard solution would be.

SELECT software, COUNT(1) 
FROM tablename
GROUP BY software
HAVING COUNT(1) = (
  SELECT MAX(sc) FROM (
    SELECT software, COUNT(1) sc
    FROM tablename
    GROUP BY software
  )
)

Note: this may return multiple rows if several pieces of software are tied for the most occurrences.

score 0 · Answer 3 · answered Sep 11 '12 at 18:41

0

You can do it in many ways but the simplest way would be this

SELECT item  
FROM (SELECT item FROM your_table GROUP BY item ORDER BY COUNT(*) desc)  
WHERE ROWNUM<=1;

answered Sep 11 '12 at 18:41

Arush Kamboj

673
3
10
20

MySQL doesn't have `ROWNUM`. – Barmar Dec 24 '15 at 10:10

Selecting the most common value from relation - SQL statement

3 Answers3

Linked

Related