Mysql: Order By with numerical value shows wrong order
Spring Boot 2.2.6.RELEASE. I have a repository method looks like this:
@Query(value = "SELECT m FROM Media m ORDER BY m.viewCount DESC") Page<Media> findMedias(Pageable pageable);
I get unordered result list with this. I tried to run the next query in the cli:
SELECT media.view_count FROM mydb.media ORDER BY media.view_count DESC;
The result looks like this:
-------------- | 9 | | 8 | | 7 | | 6 | | 5 | | 4 | | 3 | | 3 | | 20 | | 19 | | 18 | | 17 | | 16 | | 15 | | 13 | | 12 | | 12 | | 11 | | 10 | | 1 | | 1 | --------------
I want the value of 20 to be the first and not 9. Why MySQL do this kind of order? It shows one digit value first instead of the highest number?
I use sql file to create my tables.
view_count column has
LONG as type, not String. The query looks like this:
CREATE TABLE IF NOT EXISTS media(m_id INTEGER PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, category VARCHAR(10) NOT NULL, file_name VARCHAR(255) NOT NULL, view_count LONG NOT NULL, download_count LONG NOT NULL);
The view_count is stored as a string in your table which is not incorrect. If you can, then change it to integer. If you can not do that then, use the below to get the desired output.
SELECT m.view_count FROM media m ORDER BY CAST(m.view_count AS UNSIGNED) DESC;