Solved

ORACLE SQL ROWNUM

Posted on 2014-12-29
9
195 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now