Selecting the most common value from relation - SQL statement

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.

---

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

---

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.

---

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;