CODEWITHSUNDEEP
oracle-database
Belinda
Belinda

Reputation: 116

Fetching Lastrow from the table in toad

I have to fetch the first and last row of the table in Toad. I have used the following query

select * from grade_master where rownum=(select max(rownum) from grade_master)
select * from grade_master where rownum=1

The second query works to fetch the first row. but the first not working. Anyone please help me.

Thanks in advance

Upvotes: 0

Views: 1536

Answers (2)

Brian McGinity
Brian McGinity

Reputation: 5935

In oracle the data is not ordered until you specify the order in you sql statement.

So when you do: 

 select * from grade_master

oracle will give the rows in anyway it want wants.

OTOH if you do 

 select * from grade_master order by id desc

Then oracle will give the rows back ordered by id descending.

So to get the last row you could do this:

 select *
    from (select * from grade_master order by id desc)
   where rownum = 1

The rownum is determined BEFORE the "order by" clause is assessed, so what this query is doing is ordering the rows descending (the inside query) and then giving this ordered set to the outer query. The outer gets the first row of the set then returns it.

Upvotes: 0

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

Such request makes sense if you specify sort order of the results - there are no such things in database as "first" and "last" rows if sort order is not specified.

SQL> with t as (
  2  select 'X' a, 1 b from dual union all
  3  select 'C' , 2  from dual union all
  4  select 'A' a, 3 b from dual
  5  )
  6  select a, b, decode(rn, 1, 'First','Last')
  7  from (
  8  select a, b, row_number() over(order by a) rn,
  9  count(*) over() cn
 10  from t
 11  )
 12  where rn in (1, cn)
 13  order by rn
 14  /

A          B DECOD                                                              
- ---------- -----                                                              
A          3 First                                                              
X          1 Last

Upvotes: 1

Related Questions