0

I have seen lots of question like this on SO and I tried to implement the answers into my particular situation but I am having no luck. Some help would be greatly appreciated.

Query

SELECT `avatar` from `users` ORDER BY ABS(`avatar`) ASC;

Result

+--------------------------------+
| avatar                         |
+--------------------------------+
| 0/1_default.jpg                |
| 1/3_483487-1440x900_qp8a5a.jpg |
| 1/122_default.jpg              |
| 1/321_default.jpg              |
| 1/25_wefvvv.jpg                |
| 1/1000_latest.jpg              |
| 2/12_wefwefwef.jpg             |
| 2/1_default.jpg                |
+--------------------------------+

I tried to sort by ABS and columns but unless I made a new column or a dedicated table, I cannot find a way to sort this the way I want it to.

Essentially, I want to sort it numerically and a desired outcome would be something like:

Desired result

0/1
1/3
1/25
1/122
1/321
2/1
2/12

From the searches on SO, I know there is the SUBSTR function but with the '/' in the middle, I am not sure how I can get it to sort properly.

Kevin Jung
  • 2,973
  • 7
  • 32
  • 35
  • possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) – nmaier Aug 19 '13 at 01:48
  • Your better off when you separate the 0/1 and put that in a separate column that way you are still able to use indexes. peterm solution is a nice find but it can't make proper use of an index a full index scan is required if you had an index on column avatar. if your table has many records you could notice a drop in performance with that query. – Raymond Nijland Aug 19 '13 at 11:15
  • Thanks for the input! I will have to run some performance tests. – Kevin Jung Aug 19 '13 at 19:06

1 Answers1

3

UPDATED If the format for avatar is fixed then you can do it like this

SELECT avatar
  FROM users
 ORDER BY 1 * SUBSTRING_INDEX(avatar, '/', 1),
          1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1),
          SUBSTR(avatar, INSTR(avatar, '_') + 1)
  • 1 * SUBSTRING_INDEX(avatar, '/', 1) gets everything before first / and converts it to a numeric value
  • 1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1) gets everything between / and first _ and converts it to a numeric value
  • and finally SUBSTR(avatar, INSTR(avatar, '_') + 1) gets everything as it is to right after _

Output:

+--------------------------------+
| avatar                         |
+--------------------------------+
| 0/1_default.jpg                |
| 1/3_483487-1440x900_qp8a5a.jpg |
| 1/25_wefvvv.jpg                |
| 1/122_default.jpg              |
| 1/321_default.jpg              |
| 1/1000_latest.jpg              |
| 2/1_default.jpg                |
| 2/12_wefwefwef.jpg             |
+--------------------------------+

Here is SQLFiddle demo

To get the max avatar value based on your sort order

SELECT avatar
  FROM users
 ORDER BY 1 * SUBSTRING_INDEX(avatar, '/', 1) DESC,
          1 * SUBSTRING_INDEX(SUBSTRING_INDEX(avatar, '_', 1), '/', -1) DESC,
          SUBSTR(avatar, INSTR(avatar, '_') + 1) DESC
 LIMIT 1

Output:

|             AVATAR |
----------------------
| 2/12_wefwefwef.jpg |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • This works great! If its not a bother, could you help me understand the query? Thanks again. – Kevin Jung Aug 19 '13 at 01:52
  • Also, I am trying to grab the highest number from this sort. Is there a way to this? Since using `LIMIT 1` returns the lowest value. Thanks! – Kevin Jung Aug 19 '13 at 02:00
  • @usr122212 You're very welcome. See updated answer for some explanation. – peterm Aug 19 '13 at 02:06
  • Now what do you mean by *...to grab the highest number from this sort...* Can you be more specific? Do you want to get `2/12` or `2/12_wefwefwef.jpg` or anything else? – peterm Aug 19 '13 at 02:07
  • Within the given example, I would like to fetch only `2/12_wefwefwef.jpg` since it contains the highest number pair. So in essence, the highest numbered string, if that makes sense. I see that the current query returns the items in an ASC order. If this could be changed to DESC, wouldn't it be possible to use `LIMIT 1` to grab `2/12_wefwefwef.jpg`? Thanks for the help! – Kevin Jung Aug 19 '13 at 02:13
  • @usr122212 Exactly. Change the order to `DESC` for all parts and use `LIMIT 1`. See updated answer and sqlfiddle example. – peterm Aug 19 '13 at 02:22
  • Nice find but ive made a comment about at the top. – Raymond Nijland Aug 19 '13 at 11:17