-
Help required on SQL
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]
Sabitabrata
-
hi
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.
-
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.
Sabitabrata
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|