CODEWITHSUNDEEP
phpmysqlsqlarrays
Ood
Ood

Reputation: 1795

Sort MySQL result by last value

I have a MySQL table set up with a field called clicks. It contains the clicks on certain banners separated by space (e.g. "9 80 47 306"). The field's type is text. I want to get the entire table row and sort by whatever the last value in that field is (in this case it would be 306).

What I have so far doesn't work (obviously):

SELECT * FROM banners WHERE active = "1" ORDER BY clicked DESC

Is there a way I can achieve this using SQL only?

Thanks in advance!

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use substring_index() with -1:

SELECT *
FROM banners
WHERE active = "1"
ORDER BY substring_index(clicked, ' ', -1) DESC;

I should add that I agree that this is a bad way to store data. You should have a junction/association table. This would have one row per banner with a clicks column.

EDIT:

As the Joachim's comment wisely notes, we might want to change this to a number for sorting. In MySQL, I prefer + 0 because it does not report errors:

SELECT *
FROM banners
WHERE active = "1"
ORDER BY substring_index(clicked, ' ', -1) + 0 DESC;

Ugg, storing numbers as strings.

Upvotes: 4

ratmalwer
ratmalwer

Reputation: 735

Try something like: Order by substr(clicked,length(clicked-3),3)

Upvotes: 1

Related Questions