Question 21074 in Mysql Question posted in
The official MySQL documentation can be found here.

The query returns wrong result when i want to find who is the shortest player in a NBA Database – Mysql

I am working with a NBA script in MySQL and I have to find out who is the shortest player in database. I am using feet as measurement and after executing the query i found out that the player the query was giving me was not the right answer.

The query is

select * from players where height=(select min(height) from players);

And it gaves me:

 '420', 'Carlos Arroyo', 'Florida International', ' 6-2', '202', 'G', 'Magic'

where 6-2 is the height.
Instead of giving me one of these results

'26', 'Brevin Knight', 'Stanford', '5-10', '170', 'G', 'Clippers'
'113', 'Nate Robinson', 'Washington', '5-9', '180', 'G', 'Knicks'
'182', 'Earl Boykins', 'Eastern michigan', '5-5', '133', 'G', 'Bobcats'
'372', 'Damon Stoudamire', 'Arizona', '5-10', '171', 'G', 'Spurs'
'482', 'Chucky Atkins', 'South Florida', '5-11', '185', 'G', 'Nuggets'

And if I order by height players, the result it’s a bit annoying:

'Carlos Arroyo', ' 6-2'
'Shareef Abdur-Rahim', ' 6-9'
'Louis Amundson', ' 6-9'
'Brevin Knight', '5-10'
'Damon Stoudamire', '5-10'
'Chucky Atkins', '5-11'
'Earl Boykins', '5-5'
'Nate Robinson', '5-9'
'Aaron Brooks', '6-0'
'Allen Iverson', '6-0'
'Kyle Lowry', '6-0'
'Jammer Nelson', '6-0'
'Sebastian Telfair', '6-0'
'Chris Paul', '6-0'
Tags:

2

Answers


  1. 0

    Convert the height-string to a number which you can use for numeric comparison.

    select player, height
from players
where cast(substring_index(height, '-', 1) as unsigned)*100+
      cast(right(concat('0', substring_index(height, '-', -1)),2) as unsigned) 
      in (
  select min(cast(substring_index(height, '-', 1) as unsigned)*100+
         cast(right(concat('0', substring_index(height, '-', -1)),2) as unsigned))
  from players
)

    See dbfiddle

    Login or Signup to reply.
  2. 0

    …

    where 6-2 is the height. Instead of giving me one of these results

    …

    You tell that all values '5-xx' are equivalent to each other, i.e. only value before the dash is taken into account.

    Also you tell that you need in only one output row, and any row of shown 5 rows matches – i.e. you do not need in secondary sorting.

    If so then you may simply do

    SELECT *
FROM players
ORDER BY CAST(height AS UNSIGNED) LIMIT 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search