Home » SQL & PL/SQL » SQL & PL/SQL » two oldest values (10g)
two oldest values [message #340123] Mon, 11 August 2008 09:33 Go to next message
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?
Re: two oldest values [message #340126 is a reply to message #340123] Mon, 11 August 2008 09:44 Go to previous messageGo to next message
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 ;




Re: two oldest values [message #340130 is a reply to message #340123] Mon, 11 August 2008 10:09 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference should help you... if you have a look at it: ROWNUM Pseudocolumn.

Regards
Michel
Previous Topic: HELP UNDERSTANDING CODE
Next Topic: Function Row to Column
Goto Forum:
  


Current Time: Sun Dec 04 12:35:09 CST 2016

Total time taken to generate the page: 0.11140 seconds