ORACLE SQL ROWNUM

I have this really simple table (haven't working with SQL for several years, trying to re-sharp my forgotten skills)
NAZIV = NAME, CENA = PRICE, ROBA = GOODS
table data
I am trying to select 5 cheapest products with nested select with ROWNUM like this:

SELECT naziv, cena
FROM (
SELECT naziv, cena, ROWNUM r
FROM roba
ORDER BY cena
)
WHERE r between 1 and 5;

Open in new window


but the output is:

NAZIV                      CENA
-------------------- ----------
coca cola                   ,78
solja                          1,2
olovka                      1,35
knjiga                     49,99
mobilni                   89,99

instead of like this:
NAZIV CENA
-------------------- ----------
vazduh                       0
coca cola                 .78
jaje                            .8
dollar                         1
jabuka                   1.05

Even if I go like this:

SELECT naziv, cena
FROM (
SELECT naziv, cena, 
ROWNUM r FROM ROBA 
ORDER BY CENA ASC )
WHERE r BETWEEN 1 and 5;

Open in new window


Output is not correct;
NAZIV                      CENA
-------------------- ----------
coca cola                   ,78
solja                          1,2
olovka                      1,35
knjiga                     49,99
mobilni                   89,99

so it just displays...I don't know in which order...
But if I do only this

SELECT naziv, cena FROM ROBA ORDER BY CENA asc;

Open in new window


Then it sorts it out correctly:
NAZIV                      CENA
-------------------- ----------
vazduh                        0
coca cola                   ,78
jaje                         ,8
dollar                        1
jabuka                     1,05
solja                       1,2
cd/r                        1,2
olovka                     1,35
boja                       2,22
hleb                        2,5
ranac                     21,53
knjiga                    49,99
mobilni                   89,99
tepih                     122,4
vrata                       150
sto                         198
tv                          310
laptop                    999,5
bicikl                     1250
oracle                    19999

My question is:
Where did I go wrong with this:

SELECT naziv, cena
FROM (
SELECT naziv, cena, ROWNUM r
FROM roba
ORDER BY cena
)
WHERE r between 1 and 5;

Open in new window


and how should I resolve it?
KPaxAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
not sure whether 0 is really stored or it is a null value which is shown as 0 in the data grids depending on the options/preferences of the UI tool.

can u try :

select naziv, cena from (
SELECT naziv, cena FROM ROBA ORDER BY CENA asc )
where rownum < 6
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :

select naziv, cena from ( SELECT naziv, cena , rownum rr FROM ROBA ORDER BY CENA asc )
where rr < 6
0
 
KPaxAuthor Commented:
Nope, result is the same:
NAZIV                      CENA
-------------------- ----------
coca cola                   ,78
solja                       1,2
olovka                     1,35
knjiga                    49,99
mobilni                   89,99
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
KPaxAuthor Commented:
Yes, 0 is really stored.
Your solution works,
Thank you.

Can you add brief explanation why do you think that previous solutions such as:

SELECT naziv, cena
FROM (
SELECT naziv, cena, ROWNUM r
FROM roba
ORDER BY cena
)
WHERE r between 1 and 5;

were actually returning first five rows sorted by 'price' instead of first 5 rows of already sorted by price ROWNUm r ?

Also, I just found one more solution:

select naziv, cena
  FROM (
    SELECT naziv, cena, row_number()  OVER (order by cena) r
      from roba
  )
where r between 1 and 5;

Open in new window


The other soulution even makes it possible to get other range of results like
where r between 5 and 10;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
sorry, ignore this "not sure whether 0 is really stored or it is a null value which is shown as 0 in the data grids depending on the options/preferences of the UI tool."

I typed a different response initially and then forgot to remove.

Later I realized that the initial query itself is wrong. so I setup a test table and got that working query for you. No doubts I have forgot a lot which I used used to remember easily several years before :)

to explain why the prev one did not work, just execute the inner one and see for youself.

SELECT naziv, cena, ROWNUM r
 FROM roba
 ORDER BY cena

Rownum is populated while the records were retrieved and then order by does the sort. you got what I mean "r" gets values as per the records inserted into the table/records retrieved by the query before order by was done and we have used that incorrect r to filter out only first 5 records and that is why it was incorrect.
0
 
KPaxAuthor Commented:
I just found one more solution:

select naziv, cena
  FROM (
    SELECT naziv, cena, row_number()  OVER (order by cena) r
      from roba
  )
where r between 1 and 5;

Open in new window


The other solution even makes it possible to get other range of results like
where r between 5 and 10;

Anyway, thank you for the quick fix :)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yep.. that sounds better using an analytical function and as I said it is many years I did these queries.

Otherwise I should have recommended the row_number() over ... as well to you with the rownum stuff.

Thanks,
0
 
KPaxAuthor Commented:
And I made one more, posting it here just for future reference when I again forget how to do it :)

SELECT naziv, cena from ( 
SELECT ROWNUM r, naziv, cena 
FROM (SELECT naziv, cena FROM ROBA ORDER BY CENA ASC ))
where r between 1 and 5;
-- range could be any, like 3 and 7, it doesn't have to start from 1

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
fine, good. no problem but this is a tweak based on the rownum query to make it for a given range with between instead of direct rownum filter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.