Help required on SQL

[sabita_ban]

Hi,

I have a table say XXX having the following data

STATU INTIME OUTTIME
----- --------- ---------
NO 04-DEC-01 04-DEC-01
NO 09-OCT-01 09-OCT-01
NO 28-JAN-02 28-JAN-02
NO 27-NOV-01 27-NOV-01
NO 05-OCT-01 05-OCT-01
NO 12-OCT-01 12-OCT-01
NO 17-DEC-01 17-DEC-01
NO 04-DEC-01 04-DEC-01
NO 04-DEC-01 04-DEC-01
NO 10-DEC-01 10-DEC-01
NO 06-NOV-01 06-NOV-01
NO 14-DEC-01 14-DEC-01
I need to select the rows of this table with intime being displayed in Ascending/Descending Order.
This can be done using ORDER BY INTIME clause.
But suppose I need to rank the rows ie 1,2,3...
resulting in the following output

STATU INTIME OUTTIME RANK
----- --------- --------- -------
NO 05-OCT-01 05-OCT-01 1
NO 09-OCT-01 09-OCT-01 2
NO 12-OCT-01 12-OCT-01 3
NO 06-NOV-01 06-NOV-01 4
NO 27-NOV-01 27-NOV-01 5
NO 04-DEC-01 04-DEC-01 6
NO 04-DEC-01 04-DEC-01 7
NO 10-DEC-01 10-DEC-01 8
NO 14-DEC-01 14-DEC-01 9
NO 17-DEC-01 17-DEC-01 10
NO 28-JAN-02 28-JAN-02 11

How do I get this??

NB I am using Oracle 8.1.7



[Edited by sabita_ban on 02-04-2002 at 12:54 AM]

[nishant]

Originally posted by sabita_ban
Hi,

I have a table say XXX having the following data

STATU INTIME OUTTIME
----- --------- ---------
NO 04-DEC-01 04-DEC-01
NO 09-OCT-01 09-OCT-01
NO 28-JAN-02 28-JAN-02
NO 27-NOV-01 27-NOV-01
NO 05-OCT-01 05-OCT-01
NO 12-OCT-01 12-OCT-01
NO 17-DEC-01 17-DEC-01
NO 04-DEC-01 04-DEC-01
NO 04-DEC-01 04-DEC-01
NO 10-DEC-01 10-DEC-01
NO 06-NOV-01 06-NOV-01
NO 14-DEC-01 14-DEC-01
I need to select the rows of this table with intime being displayed in Ascending/Descending Order.
This can be done using ORDER BY INTIME clause.
But suppose I need to rank the rows ie 1,2,3...
resulting in the following output

STATU INTIME OUTTIME RANK
----- --------- --------- -------
NO 05-OCT-01 05-OCT-01 1
NO 09-OCT-01 09-OCT-01 2
NO 12-OCT-01 12-OCT-01 3
NO 06-NOV-01 06-NOV-01 4
NO 27-NOV-01 27-NOV-01 5
NO 04-DEC-01 04-DEC-01 6
NO 04-DEC-01 04-DEC-01 7
NO 10-DEC-01 10-DEC-01 8
NO 14-DEC-01 14-DEC-01 9
NO 17-DEC-01 17-DEC-01 10
NO 28-JAN-02 28-JAN-02 11

Solution
==============
select b.status,b.intime,b.outtime,rownum from ( select status,intime,outtime from xxx order by intime) b ;
==============

peace
nishant.

[sabita_ban]

Thanks,

Infact I tried out a query shown below

select STATUS,INTIME,OUTTIME,rank() over(order by intime) RANK from XXX;


But yours is a much simpler and a better one.