Team, looking for a sql query to get the top priority data set.
for Example:
create table prior_test
(
emp_id number,
dept_id number,
salary number
);
insert into prior_test values(10,20,1500);
insert into prior_test values(20,20,6500);
insert into prior_test values(30,20,1800);
insert into prior_test values(40,40,1500);
insert into prior_test values(50,40,6500);
insert into prior_test values(60,40,1800);
commit;
select emp_id,dept_id,salary,priority_seq
from
(
select emp_id,dept_id,salary,1 priority_seq from prior_test where dept_id=10
union
select emp_id,dept_id,salary,2 priority_seq from prior_test where dept_id=20
union
select emp_id,dept_id,salary,3 priority_seq from prior_test where dept_id=30
union
select emp_id,dept_id,salary,4 priority_seq from prior_test where dept_id=40
)
;
I need output as below:
2 is my priority_seq in this result compared to 4. So, I need output that has priority_seq=2 but the priority_seq could be 1,2,3 or 4. Need a sql for this output.