How to find highest salary ? [message #660766] |
Sun, 26 February 2017 21:59 |
|
asliyanage
Messages: 60 Registered: January 2017
|
Member |
|
|
I need to find the highest salary from employees table. I used below query.Its working fine.
select e.FIRST_NAME from employees e
where salary=(select max(salary) from employees);
without sub query is there a way to find the highest salary ?
|
|
|
|
|
|
Re: How to find highest salary ? [message #660794 is a reply to message #660768] |
Mon, 27 February 2017 07:01 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Sun, 26 February 2017 23:09
select max (first_name) keep (dense_rank last order by salary) as first_name
from employees
/
Careful!. This query isn't deterministic. Therefore someting like:
select max (first_name) keep (dense_rank last order by salary) as first_name,
max (last_name) keep (dense_rank last order by salary) as last_name
from employees
/
May return first name of employee X and last name of employee Y if both earn highest salary:
SQL> select *
2 from employees
3 /
FIRST_NAME LAST_NAME SALARY
---------- ---------- ----------
John Smith 50000
Sam Jones 50000
SQL> select max (first_name) keep (dense_rank last order by salary) as first_name,
max (last_name) keep (dense_rank last order by salary) as last_name
from employees
/
FIRST_NAME LAST_NAME
---------- ----------
Sam Smith
SQL>
We need to add something unique to order by clause. For example:
select max (first_name) keep (dense_rank last order by salary,rowid) as first_name,
max (last_name) keep (dense_rank last order by salary,rowid) as last_name
from employees
/
FIRST_NAME LAST_NAME
---------- ----------
Sam Jones
SQL>
SY.
|
|
|
|
|
Re: How to find highest salary ? [message #660799 is a reply to message #660766] |
Mon, 27 February 2017 08:16 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize solution (12C):
select first_name,
last_name,
salary
from hr.employees
match_recognize(
order by salary desc
measures first(first_name) as first_name,
first(last_name) as last_name,
first(salary) as salary
pattern(down+)
define down as 1 = 1
)
/
FIRST_NAME LAST_NAME SALARY
---------- ---------- ----------
Steven King 24000
SQL>
Or:
select first_name,
last_name,
salary
from hr.employees
match_recognize(
order by salary
measures first_name as first_name,
last_name as last_name,
salary as salary
pattern(down+)
define down as 1 = 1
)
/
FIRST_NAME LAST_NAME SALARY
---------- ---------- ----------
Steven King 24000
SQL>
SY.
|
|
|
|
Re: How to find highest salary ? [message #660835 is a reply to message #660825] |
Mon, 27 February 2017 10:18 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Oracle says otherwise - WITH clause:
subquery_factoring_clause::=query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]
[, query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...
In-line view:
table_reference::={ { { ONLY (query_table_expression) | query_table_expression }
[ flashback_query_clause ]
[ pivot_clause | unpivot_clause | row_pattern_clause ] }
| containers_clause
}
[ t_alias ]
query_table_expression::={ query_name
| [ schema. ]
{ table [ partition_extension_clause
| @ dblink
]
| { view | materialized view } [ @ dblink ]
} [sample_clause]
| [ LATERAL ] (subquery [ subquery_restriction_clause ])
| table_collection_expression
}
Also, in-line view != CTE (WITH clause). In-line view is never materialized.
SY.
|
|
|
Re: How to find highest salary ? [message #660839 is a reply to message #660835] |
Mon, 27 February 2017 11:58 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I didn't say to use WITH just to use analytic function which can be in FROM clause and so an inline view.
SQL> select FIRST_NAME, LAST_NAME, SALARY
2 from (select FIRST_NAME, LAST_NAME, SALARY,
3 rank() over (order by SALARY desc) rk
4 from employees)
5 where rk = 1
6 /
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Steven King 24000
I'm referring to the original "subquery" definition but you're right an inline is defined as:
( subquery )
Maybe I'm too old but what I call "subquery" is what is now called "nested subquery" or "correlated subquery".
But if you go in this way every query is with a subquery as the definition of a query is:
subquery [ for_update_clause ] ;
Quote: In-line view is never materialized
I don't know what you mean by that.
[Edit: add the query I meant]
[Updated on: Mon, 27 February 2017 12:16] Report message to a moderator
|
|
|
Re: How to find highest salary ? [message #660842 is a reply to message #660839] |
Mon, 27 February 2017 12:21 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 27 February 2017 12:58
I don't know what you mean by that.
SQL> explain plan for
2 with t as (
3 select deptno,max(sal) sal from emp group by deptno
4 )
5 select *
6 from t t1,t t2
7 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3656900876
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 468 | 12 (9)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6642_BCE237FF | | | | |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 9 | 468 | 8 (0)| 00:00:01 |
| 6 | VIEW | | 3 | 78 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6642_BCE237FF | 3 | 21 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 3 | 78 | 8 (0)| 00:00:01 |
| 9 | VIEW | | 3 | 78 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6642_BCE237FF | 3 | 21 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
17 rows selected.
SQL> explain plan for
2 select *
3 from (
4 select deptno,max(sal) sal from emp group by deptno
5 ) t1, (
6 select deptno,max(sal) sal from emp group by deptno
7 ) t2
8 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2941915396
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 468 | 16 (25)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 9 | 468 | 16 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 3 | 78 | 16 (25)| 00:00:01 |
| 6 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
15 rows selected.
SQL>
SY.
|
|
|
Re: How to find highest salary ? [message #660843 is a reply to message #660842] |
Mon, 27 February 2017 12:32 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes, this is one of strong point of factoring (in addition to legibility, and is why I almost always use it) to do this automatically when appropriate but this does not mean you can't do it with inline view (actually this was how we did it before WITH clause exists):
SQL> explain plan for
2 select *
3 from (
4 select deptno,max(sal) sal from emp where rownum >= 1 group by deptno
5 ) t1, (
6 select deptno,max(sal) sal from emp where rownum >= 1 group by deptno
7 ) t2
8 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 83283137
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 468 | 16 (25)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 9 | 468 | 16 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 3 | 78 | 16 (25)| 00:00:01 |
| 8 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 9 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 10 | COUNT | | | | | |
|* 11 | FILTER | | | | | |
| 12 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM>=1)
11 - filter(ROWNUM>=1)
Or now as MATERIALIZE hint is documented and so can be used:
SQL> explain plan for
2 select *
3 from (
4 select /*+ materialize */ deptno,max(sal) sal from emp group by deptno
5 ) t1, (
6 select /*+ materialize */ deptno,max(sal) sal from emp group by deptno
7 ) t2
8 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 2941915396
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 468 | 16 (25)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 9 | 468 | 16 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 3 | 78 | 16 (25)| 00:00:01 |
| 6 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
|
|
|
|
|
|
Re: How to find highest salary ? [message #660856 is a reply to message #660855] |
Mon, 27 February 2017 14:12 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Also you'll find Tom Kyte uses it since 2007 on AskTom when he said it was not documented before and advise to use "rownum > 0".
For instance.
In this topic
in 2005 he said:
"i like the rownum > 0 just to "materialize" the collection into temp, to avoid plsql from being invoked more than once. "
and in 2007
"Using materialize in the following to make it so that plsql function gets called ONCE."
In this topic
in 2005
"adding rownum to the select list, or using the materialize view (not documented) should do it."
and in 2009
"materialize hint would probably do it, but as you can see, it is not going to be "assured" "
...
[Updated on: Mon, 27 February 2017 14:13] Report message to a moderator
|
|
|
Re: How to find highest salary ? [message #660857 is a reply to message #660853] |
Mon, 27 February 2017 14:23 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Instead of rank, I would use dense_rank because
1) it shows everyone with that particular salary (as does rank)
2) you can do something like who has the second highest salary by simply saying =2 instead of =1
select first_name,last_name,salary
from
(select first_name,last_name,salary,
dense_rank() over (order by salary desc) rn
from hr.employees)
where rn = 1
order by last_name,first_name;
[Updated on: Mon, 27 February 2017 14:24] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to find highest salary ? [message #661265 is a reply to message #661263] |
Mon, 13 March 2017 06:20 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you don't define an order when querying data from oracle then oracle returns data in a random order.
A lot of people don't realise this since a lot of the time oracle will return data in the same order repeatedly and for small tables that will often be the order of insertion. But it doesn't have to be and oracle can return the data in a different order at any time unless you explicitly tell it not to with an order by.
Rows in tables don't have a row number. We can assign a row number when we query data, but because of the above the same row can end up with a different row number on different executions of the same query.
For this you have two choices
1) I don't care which row, give me any, if the query gives different results each time it's run it doesn't matter - in which case just add AND rownum = 1
2) I need the first one by the order a(,b(,c...)) - In which case the things you are ordering by need to be columns in the table or values that can be calculated from the columns in the table. If you want it in the order of insertion then you need a column in the time that stores when the row was inserted.
|
|
|
|
|
|
|
|
|
|