0

hey i have an database project and i want to get the last row from my table gadgets, for the first row i wrotte this and it worked

SELECT *
  from Gadgets
where ROWNUM =1

but for the last index for example 100 it doesn't work

SELECT *
  from Gadgets
where ROWNUM =100

how i have to writte my comand that i will work, i work on oracle

MT0
  • 86,097
  • 7
  • 42
  • 90
need_help_1234
  • 21
  • 1
  • 5
  • `rownum = 1` does not give the "first row in the table", whatever that means. It gives the first row to be returned by the query, and that will depend on how the data is physically stored and how optimiser decides to execute the query. – William Robertson Jan 24 '21 at 19:10

1 Answers1

2

There is no such thing as a "first" or "last" row in the table. SQL tables represent unordered sets (technically multi-sets because duplicates are allowed). You need an order by.

If you have an incremental id or created date, then you can use that to define "first" or "last". For the "first" row:

select t.*
from t
order by <ordering column>
fetch first 1 row only;

And for the "last" row:

select t.*
from t
order by <ordering column> desc
fetch first 1 row only;
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624