Home » SQL & PL/SQL » SQL & PL/SQL » How to find highest salary ?
How to find highest salary ? [message #660766] Sun, 26 February 2017 21:59 Go to next message
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 #660768 is a reply to message #660766] Sun, 26 February 2017 22:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
HR@orcl_12.1.0.2.0> select max (first_name) keep (dense_rank last order by salary) as first_name
  2  from   employees
  3  /

FIRST_NAME
--------------------
Steven

1 row selected.
Re: How to find highest salary ? [message #660777 is a reply to message #660768] Mon, 27 February 2017 03:09 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok. tnx. anyway these keywords i heard today .

1) keep
2) dense_rank last order by
Re: How to find highest salary ? [message #660784 is a reply to message #660777] Mon, 27 February 2017 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also do it with an inline view and ROW_NUMBER, DENSE or DENSE_RANK analytic function depending on what you want to display in case of even salary.

Re: How to find highest salary ? [message #660794 is a reply to message #660768] Mon, 27 February 2017 07:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #660796 is a reply to message #660794] Mon, 27 February 2017 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
We need to add something unique to order by clause.
... or use another function as I mentioned earlier.

Re: How to find highest salary ? [message #660798 is a reply to message #660796] Mon, 27 February 2017 08:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, OP wanted without subquery (and I assume without WITH clause).

SY.
Re: How to find highest salary ? [message #660799 is a reply to message #660766] Mon, 27 February 2017 08:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #660825 is a reply to message #660798] Mon, 27 February 2017 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solomon Yakobson wrote on Mon, 27 February 2017 15:04
Well, OP wanted without subquery (and I assume without WITH clause).

SY.
An inline view is not a subquery.

Re: How to find highest salary ? [message #660835 is a reply to message #660825] Mon, 27 February 2017 10:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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: Smile
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #660852 is a reply to message #660843] Mon, 27 February 2017 13:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Materialize hint is a) undocumented; b) works only in CTE.

SY.
Re: How to find highest salary ? [message #660853 is a reply to message #660852] Mon, 27 February 2017 13:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is already documented? I didn't know.

SY.
Re: How to find highest salary ? [message #660855 is a reply to message #660853] Mon, 27 February 2017 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Since 11g in MOS only not in doc, don't remember if it was R1 or R2.
The status was/is if you have a problem with this hint, Oracle tries to fix it and don't throws you as using undocumented feature.

Re: How to find highest salary ? [message #660856 is a reply to message #660855] Mon, 27 February 2017 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
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 #660858 is a reply to message #660857] Mon, 27 February 2017 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said "do it with ... ROW_NUMBER, DENSE or DENSE_RANK analytic function depending on what you want to display in case of even salary".
I used RANK because it has the same result than DENSE_RANK for rank 1 and less characters. Smile

Re: How to find highest salary ? [message #660859 is a reply to message #660858] Mon, 27 February 2017 14:30 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Sorry Michel, I missed that commit.
Re: How to find highest salary ? [message #660862 is a reply to message #660856] Mon, 27 February 2017 16:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
In any case, hint MATERIALIZED is (as far as I know) is discarded if used outside CTE. And, as usual, optimizer is free to take the hint or not.

SY.
Re: How to find highest salary ? [message #660870 is a reply to message #660862] Tue, 28 February 2017 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
optimizer is free to take the hint or not.
Correct, this is why if you want to be sure it materializes the subquery (in CTE or inline view) better use rownum trick.

Re: How to find highest salary ? [message #661257 is a reply to message #660870] Mon, 13 March 2017 05:00 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I use below query with dense_rank function

select * from
(select first_name,salary ,
dense_rank() over(order by  salary  desc) R
from employees);

but here it shows all the records from highest salary to law salary> If i need to limit the number of records how can i do that ?
Re: How to find highest salary ? [message #661258 is a reply to message #661257] Mon, 13 March 2017 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read Bill's post?
What is the purpose of your R column?

Re: How to find highest salary ? [message #661259 is a reply to message #661258] Mon, 13 March 2017 05:18 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok, i edit the query as below.

select * from
(select first_name,salary ,
dense_rank() over(order by  salary  desc) R
from employees)
where R=2;

now i am getting two records as below.

Neena	17000	2
Lex	17000	2

this is fine, anyway if i need to show first one only from this two records , how can i edit the query ?
Re: How to find highest salary ? [message #661260 is a reply to message #661259] Mon, 13 March 2017 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well which one do you want?
Re: How to find highest salary ? [message #661261 is a reply to message #661260] Mon, 13 March 2017 05:48 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
if need to show 2nd highest salary, but if there are multiple salaries i need to show first one from them.
Re: How to find highest salary ? [message #661262 is a reply to message #661261] Mon, 13 March 2017 05:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Define "first" based on your output.
Re: How to find highest salary ? [message #661263 is a reply to message #661262] Mon, 13 March 2017 05:59 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
based on the rownumber
Re: How to find highest salary ? [message #661264 is a reply to message #661263] Mon, 13 March 2017 06:15 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I found that i can get the result from below query.

select * from
( select first_name,salary,dense_rank()
 over(order by salary desc) R
 from employees 
)
where R=2 and ROWNUM<2;

i check whether i can get the 2nd value using blow query

select * from
( select first_name,salary,dense_rank()
 over(order by salary desc) R
 from employees 
)
where R=2 and ROWNUM<1 and ROWNUM<3;

but it failed.
Re: How to find highest salary ? [message #661265 is a reply to message #661263] Mon, 13 March 2017 06:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: How to find highest salary ? [message #661266 is a reply to message #661265] Mon, 13 March 2017 06:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well rownum < 1 could never ever be true. You don't get row zero.
That said, what you probably meant to code:
rownum > 1 and rownum < 3
will never work either, see this for why:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:76812348057
Re: How to find highest salary ? [message #661267 is a reply to message #661264] Mon, 13 March 2017 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, then DENSE_RANK is NOT the appropriate function for you.
Given what you posted you should read Database SQL Reference.

Re: How to find highest salary ? [message #661268 is a reply to message #661266] Mon, 13 March 2017 07:21 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok. thanks for help
Re: How to find highest salary ? [message #661272 is a reply to message #661268] Mon, 13 March 2017 20:21 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
anyway i found that we can add rownum as below for this scenario

select first_name, salary,trn from
( select first_name, salary,ROWNUM trn,dense_rank()
 over(order by  salary desc) R
 from employees 
)
where  trn between 1 and 3

[Updated on: Mon, 13 March 2017 20:21]

Report message to a moderator

Re: How to find highest salary ? [message #661274 is a reply to message #661272] Mon, 13 March 2017 20:35 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Select first_name||' '||last_name name, salary from
( select first_name, last_name, salary,ROW_number()
 over(order by  salary desc,last_name, first_name) R
 from employees 
)
where  r between 1 and 3
Order by r 
This will return the 3 highest salaries.

[Updated on: Mon, 13 March 2017 20:37]

Report message to a moderator

Re: How to find highest salary ? [message #661289 is a reply to message #661272] Tue, 14 March 2017 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
anyway i found that we can add rownum as below for this scenario
It is a very bad ways to use ROWNUM, you can't be sure the result will be in the order you want and then give the correct result.
See Bill's post, it is the correct way to do it.

As I previously said, you don't use the correct function.
And as I repeated you many times, you MUST read Database SQL Reference.

Re: How to find highest salary ? [message #661311 is a reply to message #661289] Tue, 14 March 2017 05:49 Go to previous message
asliyanage
Messages: 60
Registered: January 2017
Member
thanks Michel and Bill
Previous Topic: Uploading csv files with different names on periodical basis.
Next Topic: dynamically creating index from same table in different schema
Goto Forum:
  


Current Time: Fri Mar 29 02:18:05 CDT 2024