Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORACLE SQL ROWNUM

Posted on 2014-12-29
9
Medium Priority
?
225 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 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

705 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