Selecting the most common value from relation - SQ

问题:

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.

回答1:

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

回答2:

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.

回答3:

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;