1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Displaying even records with columns

Discussion in 'SQL PL/SQL' started by kishore garlapati, Oct 22, 2010.

  1. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    select rownum,ename
    from emp
    group by rownum,ename
    having mod(rownum,2)=0
    order by rownum
    /

    By this query,i want to display single column along with rownum
    But i want the all columns of a table along with rownum
    could you help me on this


    Thanks in Advance
    By
    GK
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to list every column name you want returned in the select list of the rownum query, and you should probably use a subquery rather than a group by construct:

    Code (SQL):
     
    SQL> SELECT *
      2  FROM
      3  (SELECT rownum AS rn, empno, ename, job, mgr, hiredate, sal, comm, deptno
      4   FROM emp)
      5  WHERE MOD(rn,2)=0
      6  /
            RN      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
             2       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
             4       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
             6       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
             8       7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
            10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
            12       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
            14       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    7 ROWS selected.
    SQL>

     
     
  3. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    Without including the columns i.e.,by projection operator with table name like emp.*
    with this any alternate solution is having or not
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can't understand your response -- is it the same as mine absent the informative example?

    Please explain what you mean as your text is confusing.
     
  5. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    for suppose my table is having thousands of columns(multiple columns)in my table
    I dont want to mention each and every column in the projection list of a select statement
    for this what am supposing to do
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Unfortunately you have no other choice when including rownum in your query as * will produce an error:

    Code (SQL):
     
    SQL> SELECT rownum,*
      2  FROM emp
      3  GROUP BY rownum,ename
      4  HAVING MOD(rownum,2)=0
      5  ORDER BY rownum
      6  /
    SELECT rownum,*
                  *
    ERROR at line 1:
    ORA-00936: missing expression

    SQL>
    SQL> SELECT *, rownum
      2  FROM emp
      3  GROUP BY rownum,ename
      4  HAVING MOD(rownum,2)=0
      5  ORDER BY rownum
      6  /
    SELECT *, rownum
            *
    ERROR at line 1:
    ORA-00923: FROM keyword NOT found WHERE expected

    SQL>

     
    Since * expands to all defined columns in the table it cannot be preceded or followed by a comma.
     
  7. balu22777

    balu22777 Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    65
    select * from table_name
    where rowid in(select rowid from table_name
    group by rownum,rowid
    having mod(rownum,2)=0
    );
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is nice, but it doesn't answer the question.
     
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Balu, i think this is what you are expecting.

    SQL > SELECT *
    FROM
    (SELECT rownum rn, emp.*
    FROM emp)
    WHERE mod(rn,2)=0;