Oracle SQL Developer how to get top value

Question

I order the column from max repeats to min. But I only want to get the top value. The value that repeats the most.

This is code:

 SELECT foreign_key
 FROM all_person
 GROUP BY foreign_key
 ORDER BY COUNT(*) DESC;

And this is what I get:

foreign_key
--------------
        10 
         9 
         2 
         4 
         8 
         3 
         7 
         6 
         1 
         5

I only want to get value 10 (in this example). I saw alot of times that people use LIMIT but I get and error if I use LIMIT like that:

ORDER BY COUNT(*) DESC LIMIT 1;

And this is error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

`LIMIT` is how one would do this in MySQL but won't work in Oracle — David Faber, Jan 05 '15 at 17:56

score 2 · Answer 1 · answered Jan 05 '15 at 17:55

2

You can use ROWNUM in a where clause:

SELECT foreign_key
FROM
(
 SELECT foreign_key
 FROM all_person
 GROUP BY foreign_key
 ORDER BY COUNT(*) DESC
)
WHERE ROWNUM = 1;

answered Jan 05 '15 at 17:55

gmiley

Is it possible to create variable in Oracle to sotre that number 10 in some variable? @gmiley – user3127680 Jan 05 '15 at 18:07

score 2 · Accepted Answer · answered Jan 05 '15 at 17:55

2

You can do this using ROWNUM:

SELECT foreign_key FROM (
   SELECT foreign_key
     FROM all_person
    GROUP BY foreign_key
    ORDER BY COUNT(*) DESC
) WHERE rownum = 1;

That is the fastest way but might not work for more complex queries.

answered Jan 05 '15 at 17:55

David Faber

Is it possible to create variable in Oracle to sotre that number 10 in some variable? @David Faber – user3127680 Jan 05 '15 at 18:08
That's a pretty broad question, could you be more specific? – David Faber Jan 05 '15 at 19:18
It depends on what you are doing. You can create a package and procedures/functions to return that value. You can also declare a variable and: SELECT foreign_key INTO variable_name FROM... – gmiley Jan 05 '15 at 19:41

score 2 · Answer 3 · answered Jan 05 '15 at 17:57

2

The rownum method has already been described, and it works in general. In Oracle 12+, you can use fetch first 1 row only:

SELECT foreign_key
FROM all_person
GROUP BY foreign_key
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

You don't specify the version of Oracle you are using. If the latest, then this will work. This is also standard and works on some other databases.

answered Jan 05 '15 at 17:57

Gordon Linoff

Is it possible to create variable in Oracle to sotre that number 10 in some variable? @Gordon Linoff – user3127680 Jan 05 '15 at 18:21

3 Answers3