MYSQL SELECT most recent of id and ORDER BY

Question

What I am trying to do is get the most recent result for each resort_id and then ORDER BY snow_depth. The first two parts I have managed. The ordering is the part that doesn't work.

What I have so far

SELECT * FROM snow_conditions t1

NATURAL JOIN ( SELECT MAX(weather_id) AS weather_id, resort_id FROM snow_conditions 
GROUP BY resort_id ) t2 

ORDER BY snow_depth DESC

weather_id is auto incremented, so I use it instead of save_time to reduce calculation resource.

The thing that is confusing me is the result comes out in some weird partial order of snow_depth.

+-----------+------------+------------+
| resort_id | weather_id | snow_depth |
+-----------+------------+------------+
|       888 |    827     |       90   |
|       943 |    835     |       90   |
|       860 |    839     |       75   |
|       17  |    828     |       71   |
|       26  |    826     |       70   |
|       9   |    852     |       60   |           
|       16  |    831     |       292  |
|       296 |    862     |       170  |
|       20  |    843     |       168  |
|       5   |    842     |       165  |
|       36  |    838     |       160  |
|       17  |    17      |       0    |
|       26  |    26      |       0    |
+-----------+------------+------------+

When really it should appear like this

+-----------+------------+------------+
| resort_id | weather_id | snow_depth |
+-----------+------------+------------+
|       16  |    831     |       292  |
|       296 |    862     |       170  |
|       20  |    843     |       168  |
|       5   |    842     |       165  |
|       36  |    838     |       160  |
|       888 |    827     |       90   |
|       943 |    835     |       90   |
|       860 |    839     |       75   |
|       17  |    828     |       71   |
|       26  |    826     |       70   |
|       9   |    852     |       60   |           
|       17  |    17      |       0    |
|       26  |    26      |       0    |
+-----------+------------+------------+

I have tried just about every relevant looking MySQL query I could find on here but they all encounter the same issue or don't work.

EDIT: I should mention this table contains thousands of rows, with hundreds of rows for each resort_id. It's done this way so I can use it to generate a snowfall history.

Answer 1

your result already sorted by snow_depth but the snow_depth column is varchar data type. change it or you can use this:

cast(snow_depth as unsigned) DESC

so, your all query should be:

SELECT * FROM snow_conditions t1

NATURAL JOIN ( SELECT MAX(weather_id) AS weather_id, resort_id FROM snow_conditions 
GROUP BY resort_id ) t2 

ORDER BY cast(snow_depth as unsigned) DESC

Answer 2

I think you should check your database for data type of snow_depth it should not be varchar. Your result sounds like it is varchar so if it is then change it to int or any numeric type