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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Naveen KumarProduction 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.