Solved

ORACLE SQL ROWNUM

Posted on 2014-12-29
9
222 Views
Last Modified: 2014-12-29
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?
0
Comment
Question by:KPax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40523161
try this :

select naziv, cena from ( SELECT naziv, cena , rownum rr FROM ROBA ORDER BY CENA asc )
where rr < 6
0
 

Author Comment

by:KPax
ID: 40523167
Nope, result is the same:
NAZIV                      CENA
-------------------- ----------
coca cola                   ,78
solja                       1,2
olovka                     1,35
knjiga                    49,99
mobilni                   89,99
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 40523176
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Closing Comment

by:KPax
ID: 40523180
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40523185
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
 

Author Comment

by:KPax
ID: 40523189
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40523191
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
 

Author Comment

by:KPax
ID: 40523199
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40523202
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question