0

i am a newbie to SQL. I wanna find out what which player is oldest by age. So here is my table.. SQL table

Somehow my Query give error. Can you please tell me where i am doing it wrong. Thanks.

Cloudboy22
  • 1,496
  • 5
  • 21
  • 39

3 Answers3

1

SQL has a SELECT TOP command, which allows you to retrieve a set number of rows. You can do SELECT TOP 1 name AS 'Oldest Person' FROM players ORDER BY age DESC

What this will do is: first retrieve all the players, sort them by age descending (oldest first), then take the first one.

Felix Guo
  • 2,700
  • 14
  • 20
  • 1
    this is very inefficient because sort is O(n log n) but max is linear, just O(N). not sure if there is a better way in SQL though, not a SQL expert. – Tommy Aug 05 '17 at 01:26
  • That's true, but I believe MAX is used within the `SELECT` part of the statement, in which you have to first make a pass to determine the `MAX`, store it and then use a `WHERE` to find it. If the OP is interested, this question addresses that method: https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column albeit being a bit more complicated but faster over larger data sets. – Felix Guo Aug 05 '17 at 01:30
1
select * from players 
where age = (select max(age) as Oldest_Player from players);
limit 1
Robus
  • 465
  • 6
  • 19
0

You can use row_number as below:

Select * from (
   Select *, RowN = Row_Number() over(order by age desc) from Players 
   ) a Where a.RowN = 1
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38