Q:

Most occured value in database

hello by writing like this
SELECT name, COUNT(name) AS value_occurrence
FROM table
GROUP BY name
ORDER BY value_occurrence DESC;

i get this table:
name  occurrence
----  ---------
  a        6
  b        5
  c        3

But i want the name of most occurred one a.what should i write for this purpose?(i used sql)

A:

In Oracle, this will get you a name occurring as often as or more often than all other names:
SELECT name, value_occurrence
FROM (
  SELECT name, COUNT(name) AS value_occurrence
  FROM table
  GROUP BY name
  ORDER BY value_occurrence DESC )
WHERE rownum = 1;

In other words, if there are two names both having the highest number of occurrences, the above query will give you one of them (at random).  If you don't like the random aspect, you could change the ORDER BY value_occurrence DESC to ORDER BY value_occurrence DESC, name if you like.
If, in the case of a tie, you want to see all the names that are tied for 1st, you can do this:
SELECT name, value_occurrence
FROM (
  SELECT name, count(*), dense_rank() over ( partition by null order by COUNT(name) desc) AS value_occurrence_pos
  FROM table
  GROUP BY name
  ORDER BY value_occurrence DESC )
WHERE value_occurence_pos = 1;

