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!

How to display all columns from a table while using group by?!

Discussion in 'SQL PL/SQL' started by Vicky, Feb 23, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hiii..

    I wanna group the records from employees table using emp_id, @ the same time i wanna display all the columns(*) from the table after grouping the records using group by.,

    like., select * from employees group by emp_id.,

    isn't it possible to fetch all the columns.,?
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky ,

    If you are using any aggregate functions for any column then you have to use group by .

    I believe in the employees table emp_id is primary/unique. then no use of using the group by .
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I just showed Employees as sample., I've different tables where each have more than 100 columns..

    Currently, I'm fetching all columns using rowid as below...

    select * from process
    where rowid in
    (select max(rowid) from process group by pro_id)..

    But, I wanna avoid rowid.,Is der any alternative for this qry.,!
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky ,

    ok that is fine . here could you please confirm isn't that pro_id is unique in the the table process. ?

    if it is unique then group by will not affect right .

    for eg: from the employee table if you take group by dept_id then it will groups different departments . but if we take group by emp_id then it will not make any sense right .

    I believe from your example .

    select * from process

    and

    select * from process
    where rowid in
    (select max(rowid) from process group by pro_id)

    will provide you the same output .
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    yes., U're correct., Please consider tat as department_id.,

    select * from process
    where rowid in
    (select max(rowid) from process group by dept_id).,

    The qry should be like the 1 abv., Is der any alternative for dis without using rowid.,?
     
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Vicky, that depends on the requirement.

    could you please provide your sample data and your requirement.

    We could try to help you out.
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Ok., U can use data in HR schema,.

    Initially, I used the qry below to fetch only department_id column which is been grouped by department_id.,

    select count(1), department_id from hr.employees group by department_id;

    When I wanna, fetch all the column, by grouping only department_id., I used the qry below/.,

    select * from hr.employees
    where rowid in
    (select max(rowid) from hr.employees group by department_id).,


    Now i wanna avoid rowid., Is any qry possible to give same result without rowid?...