MYSQL:
SELECT * FROM book ORDER BY $sidx $sord LIMIT $start , $limit;
How do I turn this into Oracle?
4 Answers
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;
6 Comments
Daniel Hilgarth
Bob Jarvis - Слава Україні
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.Daniel Hilgarth
@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.
raina77ow
@BobJarvis Greatest thanks for both the code AND the explanation. )
|
select * from(
select rownum as row_num, id from t
)
where row_num between :start and :start+:limit
Comments
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)
/*
Comments
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
2 Comments
Niladri Biswas
please care to mention the reason for down voting. Anything wrong in the query being provided?
Bob Jarvis - Слава Україні
I don't see a downvote on this question..? (Click on the number between the upvote and downvote arrows to view the vote totals).
searchbox 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. Share and enjoy.