Proper usage of ROWNUM in Oracle SQL query

Question

I want to find the city and length of city (i.e. number of characters in the word CITY) where the length of city is maximum in the table. In case, we have more than one value we have to take only the first value on the basis of alphabetical arrangement of the city.

There is a weird thing I observe:

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY;

gives me the correct table with the desired output as the first row with Length(City) = 21. But, when I try to retrieve the first row using

SELECT CITY, LENGTH(CITY) FROM STATION WHERE ROWNUM = 1 ORDER BY LENGTH(CITY) DESC, CITY;

query in Oracle it gives me some other row which has Length(City) = 12.

Please help me understand what is going wrong! Also FYI, there is just one value with maximum length of city.

Sebastian Brosch · Accepted Answer · 2020-01-25T18:04:39.953

The WHERE clause of your query is executed before ORDER BY so the ROWNUM = 1 is not the expected row. You can use FETCH FIRST ROW ONLY instead:

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) DESC, CITY 
FETCH FIRST ROW ONLY;

... or you put the statement in a sub-select and use WHERE with ROWNUM on this:

SELECT * 
FROM (
  SELECT CITY, LENGTH(CITY) 
  FROM STATION 
  ORDER BY LENGTH(CITY) DESC, CITY
)x WHERE ROWNUM = 1;

demo on dbfiddle.uk

score 4 · Answer 2 · answered Jan 25 '20 at 17:55

rownum works just fine. The where clause is processed before the order by. You can use a subquery instead:

SELECT S.*
FROM (SELECT CITY, LENGTH(CITY)
      FROM STATION S
      ORDER BY LENGTH(CITY) DESC, CITY
     ) S
WHERE ROWNUM = 1 ;

Or, better yet, use FETCH which has been available in Oracle for many years:

SELECT CITY, LENGTH(CITY)
FROM STATION S
ORDER BY LENGTH(CITY) DESC, CITY
FETCH FIRST 1 ROW ONLY;

score 2 · Answer 3 · answered Jan 25 '20 at 18:35

2

You can also do it like this:

select city, length_city
from ( SELECT CITY
              , LENGTH(CITY) length_city
              , row_number() over(order by LENGTH(CITY) DESC, CITY) rn
       FROM STATION)
where rn =1;

Here is a small DEMO

answered Jan 25 '20 at 18:35

VBoka

8,995
3
16
24

Hi @AmitPathak, it would be nice to get your feedback. You can accept the correct answer and comment if some of the answers is not ok so we can know why is not ok and then try to help more... Cheers! – VBoka Jan 26 '20 at 12:55
Hi @VBoka can you please explain the **over** keyword you used – Amit Pathak Mar 05 '20 at 08:23
1

Hi @AmitPathak here is what I have found so I can answer you fast and helpful: https://stackoverflow.com/questions/1092120/over-clause-in-oracle Please do tell if it is not enough – VBoka Mar 05 '20 at 08:39

Proper usage of ROWNUM in Oracle SQL query

3 Answers3