SELECT most experienced person

Question

I have to make a table of basketball players and a query which finds the player with the most experience I have tried

SELECT firstName, lastName, MAX(experience) FROM Player

but I'm assuming thats wrong.

So basically I want to find the player with the highest experience (data type set as an INT)

Thank you!! :D

possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — APC, Apr 30 '13 at 10:38

score 4 · Accepted Answer · answered Apr 30 '13 at 08:50

4

SELECT  firstName, 
        lastName, 
        experience
FROM    Player
WHERE   experience = (SELECT MAX(experience) FROM Player)

answered Apr 30 '13 at 08:50

John Woo

258,903
69
498
492

2

This might return duplicates (if there are two players with maximum experience). – Frank Schmitt Apr 30 '13 at 08:51
5

@FrankSchmitt yes because both of them are the most experienced person. – John Woo Apr 30 '13 at 08:52

score 1 · Answer 2 · answered Apr 30 '13 at 08:54

1

SELECT  * FROM Player
WHERE experience = 
(SELECT max(experience) FROM Player)

answered Apr 30 '13 at 08:54

Linga

10,379
10
52
104

score 1 · Answer 3 · edited Oct 11 '14 at 18:51

1

select top 1 firstName, lastName, experience
from Player
order by experience desc;

edited Oct 11 '14 at 18:51

vc 74

37,131
7
73
89

answered Apr 30 '13 at 10:34

Karan Gandhi

1,494
2
12
20

score 0 · Answer 4 · answered Apr 30 '13 at 08:54

0

select firstName, lastName, experience
from Player
where rownum = 1
order by experience desc;

answered Apr 30 '13 at 08:54

Kevin Bowersox

93,289
19
159
189

this doesn't work if there are more than one players with the most experience. – dmg Apr 30 '13 at 09:16
-1 because this approach doesn't work at all. You have to sort first and then take the first row, not the other way round. – Frank Schmitt Apr 30 '13 at 09:36

score 0 · Answer 5 · edited Jun 27 '13 at 12:19

The correct query is:

Select FirstName, LastName, Experience as Experience_Player
from Player
where experience = (Select MAX(experience) from Player)

Suppose you have following data:

FirstName                     LastName               Experience  
Adam                          Smit                   15  
John                          Carlos                 25  
Ibrahim                       Khan                   10

When you would apply above mentioned query, you will get the name of Ibrahim Khan because he is the most experienced player.

And if you want to get multiple player having experience more than 10 years, just run this query

Select FirstName, LastName, experience 
from Players
where experience > 10

score 0 · Answer 6 · edited Oct 11 '14 at 16:21

0

SELECT FIRSTNAME,LASTNAME,EXPERIENCE FROM (SELECT FIRSTNAME,LASTNAME,EXPERIENCE,DENSE_RANK() OVER (ORDER BY EXPERIENCE DESC) EXP FROM PLAYER) WHERE EXP=1;

edited Oct 11 '14 at 16:21

rink.attendant.6

44,500
61
101
156

answered Oct 11 '14 at 16:11

Abhijit

11
2

score -2 · Answer 7 · answered Apr 30 '13 at 08:51

-2

select * from (
  select firstname, lastName, experience from player
  order by experience desc)
where rownum = 1

answered Apr 30 '13 at 08:51

Frank Schmitt

30,195
12
73
107

this does not work if more than 1 players have the same maximum experience – dmg Apr 30 '13 at 09:17
@dmg depends on your definition of "does not work" - see my comment on JW's answer. – Frank Schmitt Apr 30 '13 at 09:33

SELECT most experienced person

7 Answers7