oracle sql max value

Question

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

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

Popeye · Accepted Answer · 2020-12-11 05:52:26Z

1

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

answered Dec 11, 2020 at 5:52

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen · Accepted Answer · 2020-12-11 05:55:34Z

1

One way is to add name too in an aggregate function

select max(VAL) val, dept, max(name) name
from tst
group by dept

answered Dec 11, 2020 at 5:55

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Collectives™ on Stack Overflow

oracle sql max value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related