Display all columns of a table using group by

[m jansi]

Hi
i have the table employee with columns emp_id, emp_name, salary, dep_id and the another table department with columns dep_id, dep_name, manager.
dep_id is the foreign key for employee table.
i want the query for retrieving emp_id, salary, dep_id i.e. the employee who got maximum salary in each department using group by

[debasisdas]

Kindly post the query that you are working on.

[amitpatel66]

How about using analytical function ROW_NUMBER()

[Sandeep M]

try this..
[code=oracle]
create table employee_test (emp_id int, emp_name varchar(50), salary numeric(10,0), dep_id int )
/

create table department_test ( dep_id int, dep_name varchar(50), manager varchar(50))
/

insert into employee_test values(1,'san', 100,1)
insert into employee_test values(2,'san1' ,50,2)
insert into employee_test values(3,'san2' ,250,1)
insert into employee_test values(4,'san3' ,350,3)
insert into employee_test values(5,'san4' ,900,1)
insert into employee_test values(6,'san5' ,25,3)
insert into employee_test values(7,'san6' ,560,1)
insert into employee_test values(8,'san7' ,11,2)
insert into employee_test values(9,'san44 ',900,1)
insert into department_test values (1,'dep1','rr')
insert into department_test values(2,'dep2' ,'rr')
insert into department_test values(3,'dep3' ,'rr')

/

select cur.dep_ID,cur. salary,emp1.emp _name from (
select dep.dep_ID,max( salary) as salary from employee_test emp inner join
department_test dep on dep.dep_id=emp. dep_id
group by dep.dep_ID) cur
left join employee_test emp1
on emp1.salary =cur.salary
/
[/code]

~~~ Enjoy Knowledge Freedom ~~~

[Jaydeep Paul]

You can use analytical function for this

 create table employee_test (emp_id int, emp_name varchar(50), salary numeric(10,0), dep_id int )
 /
  
 create table department_test ( dep_id int, dep_name varchar(50), manager varchar(50))
 /
  
 insert into employee_test values(1,'san',100,1)
 insert into employee_test values(2,'san1',50,2)
 insert into employee_test values(3,'san2',250,1)
 insert into employee_test values(4,'san3',350,3)
 insert into employee_test values(5,'san4',900,1)
 insert into employee_test values(6,'san5',25,3)
 insert into employee_test values(7,'san6',560,1)
 insert into employee_test values(8,'san7',11,2)
 insert into employee_test values(9,'san44',900,1)
 insert into department_test values (1,'dep1','rr')
 insert into department_test values(2,'dep2','rr')
 insert into department_test values(3,'dep3','rr')
  
 /

select e.emp_id,e.dep_id,max(salary) over(partition by e.dep_id) from employee_test e;

if you want to show the department name then only you need to join the two tables.