CODEWITHSUNDEEP
sqloracle-databasegroup-bysql-order-byunion
RIPI
RIPI

Reputation: 351

How to select first and last record from ordered query without UNION, INTERSECT etc

So, I admit it was one of my exam tasks yesterday and I failed to deal with it... I had simple database of people with their name, salary and function (A, B, C, D, E and F) and I had to select functions that have the biggest and the lowest avg salary. I had also to ignore function C. Example of database:

name      salary      function
Mike        100          A
John        200          F
Jenny       500          B
Fred        400          B
...         250          C
...         800          D
...         100          E
...         350          E
...         450          F
...         250          A
...         500          B

Example of result:

function          avg salary
  A                  300
  C                  600

I know how to do it using UNION as in oracle I can group by function order by salary, union that with order by salary desc and for example fetch 1 rows only in both selects. I could have used WHERE but it's impossible to use WHERE with aggregation (like AVG(salary)), but how to do it in single query without UNION, MINUS or INTERSECT?

Upvotes: 0

Views: 105

Answers (4)

dnoeth
dnoeth

Reputation: 60482

If you want to return exactly one min/max row you probably get a better plan when you avoid two different ORDER BY:

select function, avg_salary
from (
  select function, avg(salary) as avg_salary,
    ROW_NUMBER() over (order by avg(salary)) as rnk_asc,
    COUNT(*) over () as cnt
  from tablename
  group by function
)
where rnk_asc = 1 or rnk_asc = cnt;

Caution, RANK will fail if there are two rows with the same MAX value (no row where rnk_asc = cnt).

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146557

Can you use subquery in Oracle ? try this

Select z.Function from 
   (Select Function, Avg(Salary) avgSal
    from tableName
    group By function) z
Where z.avgSal = (Select Max(z0.avgSal) 
                  from (Select Avg(Salary) avgSal
                        from tableName
                        group By function) z0)
  Or z.avgSal = (Select Min(z1.avgSal) 
                  from (Select Avg(Salary) avgSal
                        from tableName
                        group By function) z1)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You could use rank function (use dense_rank if there can be ties in averages) to order rows by their average salary. Then select the highest and lowest ranked rows.

select t1.function, avg(t1.salary)
from (select 
       rank() over(order by avg(salary) desc) rnk_high
      ,rank() over(order by avg(salary)) rnk_low
      ,function 
      from tablename
      group by function) t 
join tablename t1 on t.function = t1.function
where rnk_high = 1 or rnk_low = 1
group by t1.function

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191520

Similar to @vkp's approach, but without the join back to the base table:

select function, avg_salary
from (
  select function, avg(salary) as avg_salary,
    rank() over (order by avg(salary)) as rnk_asc,
    rank() over (order by avg(salary) desc) as rnk_desc
  from tablename
  group by function
)
where rnk_asc = 1 or rnk_desc = 1;

F AVG_SALARY
- ----------
D        800
A        175

The two rank() calls put each function/average in order:

select function, avg(salary) as avg_salary,
  rank() over (order by avg(salary)) as rnk_asc,
  rank() over (order by avg(salary) desc) as rnk_desc
from tablename
group by function;

F AVG_SALARY    RNK_ASC   RNK_DESC
- ---------- ---------- ----------
D        800          6          1
B    4.7E+02          5          2
F        325          4          3
C        250          3          4
E        225          2          5
A        175          1          6

That forms the inline view; the outer query then just selects the rows ranked 1 in either of the generated columns, which is D and A here.

If you had two functions with the same average salary then they would get the same rank, and both ranked as 1 then you'd see both; so you can get more than two results. That may be what you want. If not you can avoid it by defining how to break ties, either with rank() or dense_rank().

Upvotes: 2

Related Questions