OraFAQ Forum: SQL & PL/SQL » two oldest values

Home » SQL & PL/SQL » SQL & PL/SQL » two oldest values (10g)

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

two oldest values [message #340123]

Mon, 11 August 2008 09:33

McLan
Messages: 36
Registered: April 2008

Member

I need to get two oldest values of login from service table.
the service table contains:login,service_type and created_t

I tried like below:
SQL> select created_t,login,rownum from service where service_type='sms' order by created_t;

1236045438,0697742345, 2

1236390377,012345, 3

1236390560,0000, 1

my expectation is something like this
1236045438,0697742345, 1

1236390377,012345, 2

1236390560,0000, 3

I dont know why the row number is coming wrong for oldest record, it should be 1 for oldest, 2 for older and 3 for the remaining row
changing created_t asc or created_t desc giving the same result;
can some one help me here?

Report message to a moderator

Re: two oldest values [message #340126 is a reply to message #340123]

Mon, 11 August 2008 09:44

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

In short: the ORDER BY is done after SELECTING all the required rows, thus: including the value of ROWNUM!

Try writing the query like this:

SELECT q.* , ROWNUM
FROM (
     SELECT created_t , login
     FROM service
     WHERE service_type='sms'
     ORDER BY created_t
     ) q ;

Report message to a moderator

Re: two oldest values [message #340130 is a reply to message #340123]

Mon, 11 August 2008 10:09

gintsp
Messages: 118
Registered: February 2007

Senior Member

OR in 10g analytic functions also work:

select created_t,login,row_number() over (order by created_t)
from service 
where service_type='sms' 
order by created_t;

Gints Plivna

[Updated on: Mon, 11 August 2008 12:06] by Moderator

Report message to a moderator

Re: two oldest values [message #340131 is a reply to message #340123]

Mon, 11 August 2008 10:09

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL Reference should help you... if you have a look at it: ROWNUM Pseudocolumn.

Regards
Michel

Report message to a moderator

Previous Topic:	HELP UNDERSTANDING CODE
Next Topic:	Function Row to Column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 19:45:32 CST 2025