7

I have a table called workers which includes a few persons by their names, their salary and their working station. The table looks something like the following:

|Name|Station|Salary|
|Kyle|1      |2200  |
|Lisa|2      |2250  |
|Mark|3      |1800  |
|Hans|4      |1350  |

This might sound like a very obvious beginner question but I cannot get it work. I would like to select the name of the person with the highest salary. Thank you for reading, and have a nice one.

user3691006
  • 165
  • 2
  • 2
  • 7
  • 1
    do you want to include ties. If kyle and lisa both have 2250 salary should you list both of them? – gh9 May 30 '14 at 14:13
  • Check this out: [How to find third or nᵗʰ maximum salary from salary table?](https://stackoverflow.com/a/70833322/10204932) – Deepam Gupta Jan 24 '22 at 11:55

5 Answers5

6
Select name 
from table 
where salary = (select max(salary) from table)

I dont know if you want to include ties or not (if two people have the same salary and it is the max salary.

What this does is find the max salary and then uses that in the query to find all people with that salary. You will need to replace the word table with whatever your table name is.

gh9
  • 10,169
  • 10
  • 63
  • 96
3

Try this

SELECT top 1 Name
FROM tableName
ORDER BY Salary DESC
Sid M
  • 4,354
  • 4
  • 30
  • 50
  • this will not work for ties, I dont know if op needs ties to work, but if there are ties this will drop all the results. – gh9 May 30 '14 at 14:41
  • no it will not. You are selecting the first name ordering by salary descending. IF there is a tie it will not return the second person. It will only return the first result, not the second. The second result would be the tie. Try it in sqlfiddle – gh9 May 30 '14 at 14:44
  • create table foobar( name varchar(20) null, salary int) insert into foobar select 'test',300 insert into foobar select 'fail', 300 select top 1 * from foobar order by salary run it and see. it doesnt work for ties – gh9 May 30 '14 at 14:47
  • My answer is correct as per the question, question says only name not names and hence no ties are required. :) – Sid M May 30 '14 at 14:49
  • too true, just letting you know it wont work for ties. – gh9 May 30 '14 at 14:52
0

You don't mention DBMS:

select name 
from table
order by salary desc
fetch first 1 rows only

If your DBMS support OLAP functions:

select name from (
    select name, row_number() over (order by salary desc) as rn 
    from table
) where rn = 1
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
  • since op didnt mentiond DBMS i went with an ansi sql solution. – gh9 May 30 '14 at 14:26
  • Both of mine are as well, not sure if fetch first where introduced in SQL2008 or SQL2011, row_number() has been around since SQL99 – Lennart - Slava Ukraini May 30 '14 at 14:35
  • both of your answers are not ansi sql, and there is nothing wrong with that. I am just letting op know. The second one uses TSQL row_number over. And Fetch is a keyword in sql server indicating the use of a cursor. So the first one wont run on sql server and the second one will only run on sql server. My answer will run on any DBMS system. – gh9 May 30 '14 at 14:40
  • Are you suggesting that whether the statements work or not in sqlserver has anything to do with whether they are iso/ansi compliant or not? row_number() over ( ... ) is for example described in chapter 6.10 in SQL2003. You can download draft documents from http://www.wiscorp.com/SQLStandards.html and verify. As for fetch first I cant seem to find a reference for that one so I'll withdraw that. If I find it I'll post back – Lennart - Slava Ukraini May 30 '14 at 14:59
  • I cant find fetch first in the documents right now, but it is described in this wikipedia article: http://en.wikipedia.org/wiki/Select_%28SQL%29 . Look for FETCH FIRST clause. It is btw supported by SQL Server 2012. – Lennart - Slava Ukraini May 30 '14 at 15:13
0

Try to do:

SELECT TOP name 
FROM yourtable 
WHERE salary = (SELECT MAX(salary) FROM yourtable)
flavien317
  • 48
  • 1
  • 9
0

You must use subquery to get name and highest salary:

select Name, Salary from tb_name where salary=(select max(salary) from tb_name);
irufano
  • 108
  • 1
  • 11