SQL for Oracle command

Question

MYSQL:

SELECT * FROM book ORDER BY $sidx $sord LIMIT $start , $limit;

How do I turn this into Oracle?

StackOverflow is not a language translation service. If you are having a specific issue converting to Oracle, we can help with that. — RB., Sep 18 '12 at 10:52
You might want to consider using the `search` box at the top of the page to avoid downvotes and closes in future. FWIW, the canonical answer to the question "Oracle equivalent of LIMIT" is [this question](http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering). Share and enjoy. — Bob Jarvis - Reinstate Monica, Sep 18 '12 at 11:28
Thz ppl very thz for so help. And RB everyone realized what he intended, but is more interested in giving this one a perfect ok. Again thanks to those who helped;) — zmki, Sep 19 '12 at 09:25

raina77ow · Accepted Answer · 2012-09-18T11:39:15.653

3

You have to use rownum property, BUT you have to apply it to the result set already ordered AND you need to pass it from the inner query (credit to Bob Jarvis and Daniel Hilgarth: check their explanations and links given in comments as well). It should be something like this:

SELECT * FROM
(SELECT b.*, rownum as row_num FROM book b ORDER by $sidx $sord)
WHERE row_num BETWEEN $start AND $start + $limit;

edited Sep 18 '12 at 11:39

answered Sep 18 '12 at 10:52

raina77ow

91,589
12
180
210

3

-1: rownum is applied after the where clause. See [here](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) and [here](http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm) for details. Your query will return no rows. – Daniel Hilgarth Sep 18 '12 at 10:53
@DanielHilgarth Can you explain how this answer is different from [this](http://stackoverflow.com/a/486513/1229023)? And why [this](http://sqlfiddle.com/#!4/b3cf8/4) actually returns more than 'no' rows? – raina77ow Sep 18 '12 at 10:58
2

I believe that @DanielHilgarth was trying to say that the query should do something like `SELECT * FROM (SELECT b.*, rownum as row_num from book b order by $sidx $sord) where row_num between $start and $start + $limit`. You need to pull the rownum generated by the inner query out as a separate variable to make it visible by the outer query. Share and enjoy. – Bob Jarvis - Reinstate Monica Sep 18 '12 at 11:02
2

@raina77ow: If you would read the links provided you already would know the answer. Basically: The first row returned from the query has rownum = 1. The second has rownum = 2. If you say `where rownum > 1` there will be no rows returned as you would first need to return one row to have a rownum > 1. Your first link works as it is using rownum LESS THAN 50, i.e. the first 50 rows. Your fiddle works as it uses between **one** and three. Replace **one** with **two** and you will get no results. A bit hard to explain, that's why I linked authorative sources in my last comment. – Daniel Hilgarth Sep 18 '12 at 11:04
@BobJarvis Greatest thanks for both the code AND the explanation. ) – raina77ow Sep 18 '12 at 11:32
@DanielHilgarth Now I get it. ) Of course, it well explains why the answer linked (with `rownum < $smth`) works, but my will not. Thank you for this explanation. – raina77ow Sep 18 '12 at 11:34

Mahmoud Gamal · Answer 2 · 2012-09-18T11:21:46.067

1

I am not familiar with the oracle syntax, but you can use the ROW_NUMBER() function to rank the rows then select those with rank BETWEEN @start and @limit:

   SELECT *
   FROM 
   (
       select *,
           ROW_NUMBER() OVER (ORDER BY ID DESC) AS rank
       FROM book
   )
   WHERE rank BETWEEN @start AND @end
   /* OR
   WHERE BETWEEN ((@PageNum - 1) * @PageSize + 1)
     AND (@PageNum * @PageSize)
   /*

edited Sep 18 '12 at 11:21

answered Sep 18 '12 at 10:53

Mahmoud Gamal

72,639
16
129
156

score 1 · Answer 3 · answered Sep 18 '12 at 10:57

1

select * from(
select rownum as row_num, id from t
  )
where row_num between :start and :start+:limit

answered Sep 18 '12 at 10:57

codingbiz

25,120
8
53
93

Niladri Biswas · Answer 4 · 2012-09-18T11:00:08.427

0

10g Onwards

WITH CTE AS(SELECT  b.*, Row_Number() OVER(ORDER BY SIDX,SORD) AS Rn FROM BOOK b)
SELECT *
FROM CTE
WHERE Rn BETWEEN STARTLIMIT AND ENDLIMIT

edited Sep 18 '12 at 11:00

answered Sep 18 '12 at 10:53

Niladri Biswas

4,053
2
14
22

please care to mention the reason for down voting. Anything wrong in the query being provided? – Niladri Biswas Sep 18 '12 at 10:56
1

I don't see a downvote on this question..? (Click on the number between the upvote and downvote arrows to view the vote totals). – Bob Jarvis - Reinstate Monica Sep 18 '12 at 11:24

SQL for Oracle command

4 Answers4