0

I am trying to create query with kind of limit.

SELECT * FROM ALARMS WHERE OBJECT_ID=0 AND TIMESTAMP<=1525677504171 AND ROWNUM<=51 ORDER BY TIMESTAMP DESC

I use rownum but first it makes a limit and then it select ordered by. However I need to order first and then limit. I found here enter link description here that I should use FETCH FIRST 51 ROWS ONLY. Unfortunately it doesn't work.

SELECT * FROM ALARMS WHERE OBJECT_ID=0 AND TIMESTAMP<=1525677504171 ORDER BY TIMESTAMP DESC FETCH FIRST 51 ROWS ONLY;

It throws me following error:

SQL Error [933] [42000]: ORA-00933: SQL command not properly ended

oracle.jdbc.OracleDatabaseException: ORA-00933: SQL command not properly ended
MT0
  • 143,790
  • 11
  • 59
  • 117
Bob
  • 309
  • 2
  • 5
  • 17
  • 1
    You don't use `FETCH FIRST 51 ROWS ONLY` in your statement, but `FETCH FIRST 51 ROWS` –  May 17 '18 at 11:25

2 Answers2

3

You miss ONLY at the end. This syntax is available since 12c R1 if I remember correctly. Which Oracle version you use?

SELECT * FROM ALARMS WHERE OBJECT_ID=0 AND TIMESTAMP<=1525677504171 ORDER BY TIMESTAMP DESC FETCH FIRST 51 ROWS ONLY

;

Edit:

Since your version is 11g then try to use such syntax (I hope it will work;))

SELECT * 
FROM (
    SELECT a.*,ROW_NUMBER() OVER(ORDER BY TIMESTAMP DESC) rcnt 
    FROM ALARMS A WHERE OBJECT_ID=0 AND TIMESTAMP<=1525677504171) src  
WHERE src.rcnt <= 51
ORDER BY src.TIMESTAMP desc;
Jacek Wróbel
  • 1,172
  • 10
  • 17
2

FETCH FIRST is only available since Oracle 12c.

For the rownum approach, use a subquery that contains order by, then limit the rows in the enclosing query:

SELECT * FROM (
    SELECT * FROM ALARMS WHERE OBJECT_ID=0 AND TIMESTAMP<=152567750417
    ORDER BY TIMESTAMP DESC
) dt
WHERE ROWNUM<=51
Jiri Tousek
  • 12,211
  • 5
  • 29
  • 43