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..

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
/
 

~~~ 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.