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

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'

CodePudding user response：

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

CodePudding user response：

...

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