Select max value with join

Question

I have two tables. The first one is T_EMPLOYEE

create table t_employee
(
f_id        number(8, 2)              not null primary key,
f_name      varchar(200),
);

The second is T_SALARY

create table t_salary
(
f_id                number(8, 2)              not null primary key,
f_employee_id       number(8,2),
f_salary            number(8, 2)
);

ALTER TABLE t_salary ADD CONSTRAINT fk_salary 
    FOREIGN KEY (f_employee_id) REFERENCES t_employee;

I want to get max salary and the name of the corresponding employee, I wrote this query

select t_employee.f_name, MAX(f_salary) 
from t_salary  
inner join t_employee on t_salary.f_employee_id=t_employee.f_id 
group by f_name;

but the result looks like this:

Jenny 5000
Andy  3000
Mary  1000

But I want to retrive only one name of the user who have the highest salary, so what am I doing wrong?

@a_horse_with_no_name Oops,how can he return only oe result if he doesn't limit the results, maybe a subquery with `ROWNUM <= 1` — Razorphyn, Sep 01 '13 at 21:41
http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering — Razorphyn, Sep 01 '13 at 21:42

Laurence · Answer 1 · 2013-09-01T21:43:53.167

1

You can use the rownum psuedcolumn

select
  f_name,
  f_salary
from (    
  select
    t_employee.f_name, 
    MAX(f_salary) as f_salary
  from 
    t_salary  
      inner join 
    t_employee 
      on t_salary.f_employee_id=t_employee.f_id 
  group by 
    f_name
  order by
    max(f_salary) desc
  ) x
where
  rownum = 1;

edited Sep 01 '13 at 21:43

answered Sep 01 '13 at 21:34

Laurence

10,661
1
22
32

score 1 · Accepted Answer · answered Sep 01 '13 at 21:40

select f_name, 
       f_salary
from (
  select t_employee.f_name, 
         t_salary.f_salary,
         dense_rank() over (order by t_salary.f_salary desc) as rnk
  from t_salary  
    inner join t_employee on t_salary.f_employee_id=t_employee.f_id 
) t
where rnk = 1;

score 1 · Answer 3 · answered Sep 01 '13 at 21:48

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t_employee
    ("f_id" int, "f_name" varchar2(9))
;

INSERT ALL 
    INTO t_employee ("f_id", "f_name")
         VALUES (1, 'Jenny')
    INTO t_employee ("f_id", "f_name")
         VALUES (2, 'Andy')
    INTO t_employee ("f_id", "f_name")
         VALUES (3, 'Mary')
SELECT * FROM dual
;

CREATE TABLE t_salary
    ("f_id" int, "f_employee_id" int, "f_salary" int)
;

INSERT ALL 
    INTO t_salary ("f_id", "f_employee_id", "f_salary")
         VALUES (1, 1, 5000)
    INTO t_salary ("f_id", "f_employee_id", "f_salary")
         VALUES (2, 2, 3000)
    INTO t_salary ("f_id", "f_employee_id", "f_salary")
         VALUES (3, 3, 1000)
SELECT * FROM dual
;

Query 1:

select t_employee."f_name", "f_salary"
from t_salary  
inner join t_employee on t_salary."f_employee_id"=t_employee."f_id" 
where "f_salary" = (select max("f_salary") from t_salary)

Results:

| F_NAME | F_SALARY |
|--------|----------|
|  Jenny |     5000 |

Hardy · Answer 4 · 2013-09-01T21:40:29.933

0

Try this

select t_employee.f_name, f_salary 
from t_salary inner join t_employee on t_salary.f_employee_id=t_employee.f_id
where f_salary = (
    select max(f_salary) 
    from t_salary
    where rownum <= 1)

I'm not sure if there are difference in Oracle syntax but ideas if something like that

edited Sep 01 '13 at 21:40

answered Sep 01 '13 at 21:34

Hardy

1,329
2
22
33

If there's a tie for max salary, this will produce everyone with that salary. – Laurence Sep 01 '13 at 21:42

score 0 · Answer 5 · edited Sep 02 '13 at 00:10

0

I'm not quite sure I understand but I think what you're trying to do is order by salary and select top 1.

edited Sep 02 '13 at 00:10

Leigh

28,424
10
49
96

answered Sep 01 '13 at 21:34

Mike

1

there is no `top 1` in Oracle. – a_horse_with_no_name Sep 01 '13 at 21:41
Ah sorry, didn't see the Oracle tag. – Mike Sep 01 '13 at 22:05

Select max value with join

5 Answers5