i have a requirement, i need to find max only one val from each department,i need only one value even 2 person have same max value
x
drop table tst;
create table tst(val number,dept varchar2(20),name varchar2(10));
insert into tst values(1,'tamil','john');
insert into tst values(2,'tamil','krish');
insert into tst values(3,'maths','vijay');
insert into tst values(4,'maths','raja');
insert into tst values(4,'maths','vinay');
select * from tst;
VAL DEPT NAME
1 tamil john
2 tamil krish
3 maths vijay
4 maths raja
4 maths vinay
when i tried to find max i will get 2 value for maths dept
select * from tst t1
where t1.val= (select max(val) from tst t2 where t2.dept=t1.dept
group by dept);
2 tamil krish
4 maths raja
4 maths vinay
i want either
2 tamil krish
4 maths vinay
or
2 tamil krish
4 maths raja
how to achieve this ion oracle sql
Advertisement
Answer
You can use the ROW_NUMBER
as follows:
SELECT * FROM
(SELECT T.*, ROW_NUMBER(OVER PARTITION BY DEPT ORDER BY VAL DESC) AS RN
FROM TST T)
WHERE RN = 1