CODEWITHSUNDEEP
sqloracle-database
Ragav
Ragav

Reputation: 229

how to pull the last record in Oracle

Hi I have a select clause used in a sub query after doing a Order by on few columns i get multiple rows as an output from the subquery. but i need just the last record to get returned from the subquery.so that i can process it in the main query.

Select T.C1, T1.C4 as emp from
 (Select C1,C4 from t1 group by C1,C4  order by c1,c2,c3 ) T

the sub query returns

C1             C4
bal             1
Env             1
John            2

all i need is just JOHN, 2 as output

Upvotes: 1

Views: 152

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think the simplest method is using rownum with a subquery. This requires reversing your sort order:

Select T.C1, T1.C4 as emp
from (Select C1, C4
      from t1
      group by C1, C4
      order by c1 desc, c2 desc, c3 desc
     ) T
where rownum = 1;

I do note, however, that your query is syntactically incorrect, because you are ordering by non-aggregated columns.

Upvotes: 1

Ben
Ben

Reputation: 52903

In your case the simplest way would be to use the LAST (it's not well named) clause:

select max(c1) keep (dense_rank last order by c1, c2, c3) as c1
     , max(c4) keep (dense_rank last order by c1, c2, c3) as c4
  from t1

This selects the last record in the order specified and as defined by your aggregate function. MAX is just an affection here; MIN would work equally well.

I would recommend Rob van Wijk's article.

Upvotes: 2

Related Questions