OraFAQ Forum: SQL & PL/SQL » Select query with rownum=1 how to use order by

Home » SQL & PL/SQL » SQL & PL/SQL » Select query with rownum=1 how to use order by (11)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Select query with rownum=1 how to use order by [message #571584]

Tue, 27 November 2012 06:20

cplusplus1
Messages: 30
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.

Report message to a moderator

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

Roachcoach
Messages: 1032
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.

Report message to a moderator

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

Michel Cadot
Messages: 54155
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 03:24:08 CDT 2013

Total time taken to generate the page: 0.14894 seconds