Home » SQL & PL/SQL » SQL & PL/SQL » Select query with rownum=1 how to use order by (11)
Select query with rownum=1 how to use order by [message #571584] Tue, 27 November 2012 06:20 Go to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
I am using this as a subquery within a large select statement.

(select NAME_LAST from person_name where person_id=enc.person_id and ROWNUM = 1 order by person_name_id desc) as PatFirstName

I am getting issues when i am doing rownum=1 with order by clause, what is teh right way.

when i use rownum < 2 without order y clause it is workign fine.

I would like to use order by clause.

Thanks for the helpful info.

Re: Select query with rownum=1 how to use order by [message #571585 is a reply to message #571584] Tue, 27 November 2012 06:27 Go to previous messageGo to next message
Roachcoach
Messages: 1180
Registered: May 2010
Location: UK
Senior Member
select * 
from (select ..... from $TABLE order by X)
where rownum=1
/


Is how you get the 'first' record you want.

The rownum is allocated before the ordering happens, hence you need to wrap another select around it.
Re: Select query with rownum=1 how to use order by [message #571587 is a reply to message #571584] Tue, 27 November 2012 06:46 Go to previous message
Michel Cadot
Messages: 58621
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 3 Pseudocolumns
Section ROWNUM Pseudocolumn

Please read it, and please ALWAYS read the documentation before using an Oracle function, pseudo-column... EVEN if you think or it seems for you how it works.

In addition telling us: "I am getting issues when i am doing rownum=1 with order by clause," does not help in any way to know what "issue".

Regards
Michel

[Updated on: Tue, 27 November 2012 06:47]

Report message to a moderator

Previous Topic: complex hours breakdown query
Next Topic: nested tables in pl/sql
Goto Forum:
  


Current Time: Wed Jul 30 13:38:48 CDT 2014

Total time taken to generate the page: 0.13320 seconds