0

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?

jkovacs
  • 3,470
  • 1
  • 23
  • 24
user2390742
  • 117
  • 1
  • 14
  • @Dheed: there is no `limit` in Oracle –  Sep 01 '13 at 21:38
  • @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

5 Answers5

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;
Laurence
  • 10,896
  • 1
  • 25
  • 34
1
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;
1

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 |
Fabien TheSolution
  • 5,055
  • 1
  • 18
  • 30
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

Hardy
  • 1,499
  • 2
  • 26
  • 39
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.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Mike
  • 1