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.

Answer 1

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

Answer 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.