Help required on SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help required on SQL

  1. #1
    Join Date
    Jan 2002
    Posts
    65

    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

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158

    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.

  3. #3
    Join Date
    Jan 2002
    Posts
    65
    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
  •  



Click Here to Expand Forum to Full Width