Results 1 to 3 of 3

Thread: rownum qry

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: rownum qry

    SELECT a, b, c,n,
    (SELECT COUNT(*) FROM emp)
    FROM (
    SELECT e.empno a,
    e.ename b,
    TO_CHAR(e.hiredate, 'Mon/DD/YYYY HH12:MIS AM') c,
    ROWNUM AS n
    FROM emp e
    ORDER BY e.hiredate desc
    )
    WHERE n BETWEEN 1 AND 14
    /



    A B C N (SELECTCOUNT(*)FROMEMP)
    ---------- ---------- ----------------------- ---------- -----------------------
    7876 ADAMS May/23/1987 12:00:00 AM 11 14
    7788 SCOTT Apr/19/1987 12:00:00 AM 8 14
    7934 MILLER Jan/23/1982 12:00:00 AM 14 14
    7900 JAMES Dec/03/1981 12:00:00 AM 12 14
    7902 FORD Dec/03/1981 12:00:00 AM 13 14
    7839 KING Nov/17/1981 12:00:00 AM 9 14
    7654 MARTIN Sep/28/1981 12:00:00 AM 5 14
    7844 TURNER Sep/08/1981 12:00:00 AM 10 14
    7782 CLARK Jun/09/1981 12:00:00 AM 7 14
    7698 BLAKE May/01/1981 12:00:00 AM 6 14
    7566 JONES Apr/02/1981 12:00:00 AM 4 14

    A B C N (SELECTCOUNT(*)FROMEMP)
    ---------- ---------- ----------------------- ---------- -----------------------
    7521 WARD Feb/22/1981 12:00:00 AM 3 14
    7499 ALLEN Feb/20/1981 12:00:00 AM 2 14
    7369 SMITH Dec/17/1980 12:00:00 AM 1 14



    with above qry the result is coming as shown above but i want even the rownum should get changed according
    to the data. that is hiredates are ordered in a desc manner along withthat the rownum should get changed.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: rownum qry

    Do you mean this?

    SELECT a, b, c,ROWNUM,
    (SELECT COUNT(*) FROM emp)
    FROM (
    SELECT e.empno a,
    e.ename b,
    TO_CHAR(e.hiredate, 'Mon/DD/YYYY HH12:MIS AM') c,
    ROWNUM AS n
    FROM emp e
    ORDER BY e.hiredate desc
    )
    WHERE n BETWEEN 1 AND 14
    /


    A B C ROWNUM (SELECTCOUNT(*)FROMEMP)
    ---------- ---------- ----------------------- ---------- -----------------------
    7876 ADAMS May/23/2087 12:00:00 AM 1 14
    7788 SCOTT Apr/19/2087 12:00:00 AM 2 14
    7934 MILLER Jan/23/1982 12:00:00 AM 3 14
    7900 JAMES Dec/03/1981 12:00:00 AM 4 14
    7902 FORD Dec/03/1981 12:00:00 AM 5 14
    7839 KING Nov/17/1981 12:00:00 AM 6 14
    7654 MARTIN Sep/28/1981 12:00:00 AM 7 14
    7844 TURNER Sep/08/1981 12:00:00 AM 8 14
    7782 CLARK Jun/09/1981 12:00:00 AM 9 14
    7698 BLAKE May/01/1981 12:00:00 AM 10 14
    7566 JONES Apr/02/1981 12:00:00 AM 11 14

    A B C ROWNUM (SELECTCOUNT(*)FROMEMP)
    ---------- ---------- ----------------------- ---------- -----------------------
    7521 WARD Feb/22/1981 12:00:00 AM 12 14
    7499 ALLEN Feb/20/1981 12:00:00 AM 13 14
    7369 SMITH Dec/17/1980 12:00:00 AM 14 14

    14 rows selected.

  3. #3
    Join Date
    Oct 2002
    Posts
    36

    rownum qry

    thanks for giving me reply
    but i want the rows in x and y range
    thatis i can mention 5 to 10 rows also
    the below qry will not work if i give 5 to 14 rownum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •