CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user286009
user286009

Reputation: 7

How to select a particular row of query result

I have a following table in my Oracle database:

enter image description here

How can I select the course which is done by most of the students? I am trying multiple variations in following SQL query but it's not working -

select count(course) as pcourse, course 
from studies 
group by course 
order by pcourse dec;

Upvotes: 1

Views: 30428

Answers (3)

aakashgupta.0205
aakashgupta.0205

Reputation: 687

Since this is an Oracle DB, i think the following should work

SELECT * FROM
(select count(course) as pcourse, course 
from studies 
group by course 
order by pcourse dec)
WHERE ROWNUM <= 1

Reply to the Comment

That's because rownum is assigned sequentially to the rows that are returned.

So let's say you put ROWNUM = 2, your query will return the first row, and temporarily give it rownum of 1. But since it does not match the condition ROWNUM = 2, it will be discarded. Then it will go on to fetch the second row, and give it also rownum 1, and the cycle continues.

If you want to get the Nth row, you could probably do this

SELECT pcourse, course FROM
(select count(course) as pcourse, course, rownum as rn 
from studies 
group by course 
order by pcourse dec)
WHERE rn = N

where N is the required row.

Upvotes: 1

Matt
Matt

Reputation: 14341

This should work but I haven't created your test data because it is in a picture, please include as text next time

SELECT
   course
   ,COUNT(*) AS pcourse
FROM
   Studies
GROUP BY
   course
ORDER BY
   COUNT(*) DESC
FETCH FIRST ROW ONLY

I am still a little unsure of how to the fetch first row/select top 1/limit 1 for Oracle but @a-horse_with_no_name is teaching and I am learning so here is stab at it. here is a link for Oracles syntax on offest and fetch http://docs.oracle.com/javadb/10.5.3.0/ref/rrefsqljoffsetfetch.html

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You need to use rownum, which is Oracle's equivalent of TOP 1 (from SQL Server) or LIMIT 1 (from MySQL).

select *
from 
    (select count(course) as pcourse
        , course
    from studies
    group by course
    order by pcourse desc) result_set
where rownum = 1

It is important that you use where rownum = 1 in a higher level SELECT because Oracle requires you to first sort the result set and then it will filter the rows.

Otherwise, if you would include the where rownum = 1 condition in the same level as the SELECT with which you get your data, then it will be immediately be filtering to 1 row (the first it finds) and sorting it (which won't make any sense, since it will only be one row).

As per @a_horse_with_no_name's comment, apparently Oracle gives you the possibility to write a TOP 1 filtering condition in the same level SELECT, like below:

select count(course) as pcourse
    , course
from studies
group by course
order by pcourse desc
fetch first 1 rows only;

Upvotes: 1

Related Questions