Suppose there is a table TABLE

ID | DEF 1 | Один 2 | Два 3 | Три 4 | Четыре

Then request in MS SQL Server

 SELECT TOP(2) * FROM TABLE ORDER BY ID DESC

Will give us the issue:

 ID | DEF 4 | Четыре 3 | Три

And a similar query in Oracle (analog, which was offered to me on the Internet)

 SELECT * FROM TABLE WHERE ROWNUM <= 2 ORDER BY ID DESC

Will give us the issue:

 ID | DEF 2 | Два 1 | Один

How to get really similar functionality syntax for Oracle?

  • there is no such thing here unless the subquery via row_number, row does not guarantee sorting - heff

3 answers 3

In Oracle up to version 11 inclusive - through a subquery with row_number :

 select * from (select t.*, row_number() over (order by id desc) rn from t) where rn <= 2

In the 12th version, the syntax for the first N lines with LIMIT and OFFSET , almost as in postgres (a copy request from the Internet, there is no oracles on hand at all):

  select * from t order by id desc OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

    The fact is that Oracle first performs the where clause and only then does the sorting. That is, in your case, first there are lines with id in (1, 2) and then they are sorted.

    To get a similar result, you first need to sort all the data and then wrap the query to get the required number of lines:

     SELECT * FROM (SELECT * FROM TABLE ORDER BY ID DESC) WHERE ROWNUM <= 2

      You will have to use a subquery. Those. transfer your query with basic conditions and sorting to a subquery, and put a restriction on the choice in the WHERE external query.

       SELECT * FROM ( SELECT * FROM TABLE ORDER BY ID DESC ) WHERE ROWNUM <= 2
      • Sorry, wrong. I'll fix it tomorrow. - Anatoly Ernst