1

I'm trying to sort my records from big to small numbers, here are my example records:

id   name   view   like
1   name1    5      1
2   name2    2      4
3   name3    41     3
4   name4    6      31
5   name5    1      2

For example, I want the order the records like this (i.e. ordered by the field named like):

id   name   view   like
4   name4    6      31
2   name2    2      4
3   name3    41     3
5   name5    1      2
1   name1    5      1

I tried this query...

SELECT * FROM tablename ORDER BY like DESC

...but the query result is like this now:

id   name   view   like
2   name2    2      4
3   name3    41     3
4   name4    6      31
5   name5    1      2
1   name1    5      1

So, what is the correct query for what I want?

w5m
  • 2,286
  • 3
  • 34
  • 46
Sajad Rahmanipour
  • 397
  • 1
  • 3
  • 20

5 Answers5

4

like is a reserved word in mysql

You can escape it by using backticks; ``

SELECT * FROM `tablename` ORDER BY `like` DESC
Arth
  • 12,789
  • 5
  • 37
  • 69
Saty
  • 22,443
  • 7
  • 33
  • 51
3

Looks like you are storing the numerical value of like in a string format; either CHAR or VARCHAR. This will result in the alphabetical style ordering that you can see (imagine 1 as A, 2 as B, etc.. and then order the results as words).

Change the datatype of the column to a numerical format like INT and the query should behave as expected.

If you cannot change the datatype of the column then I would suggest casting it in the query:

SELECT * FROM tablename ORDER BY CAST(`like` AS UNSIGNED) DESC

or 

SELECT * FROM tablename ORDER BY CAST(`like` AS SIGNED) DESC

if like can be negative.

I find this more readable and controlled than +0, but Abhik beat me to it :)

Arth
  • 12,789
  • 5
  • 37
  • 69
  • typecasting in mysql is also answered here: http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql In the context of this question, the typecasting should probably look something like this: `select ... cast(\`like\` as unsigned) as liked order by liked` – andkrup Jun 04 '15 at 11:12
  • @andkrup Fair enough, I quite like that approach but it is not necessary to get the results.. N.B. this would also apply to Abhik's answer below. – Arth Jun 04 '15 at 11:18
  • no disagreement at all :) I just thought that the cast function needed mentioning as well since type juggling was the issue – andkrup Jun 04 '15 at 11:24
2

This happens when you choose wrong datatype for storing integer data and you might have have choosen varchar for this.

You need to convert the string to numbers for ordering

SELECT * FROM tablename ORDER BY `like`+0 DESC
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
0

You can't use a reserved word as a field name. Change it to likes and then you can directly fire this query...

 SELECT * FROM tablename ORDER BY likes DESC
w5m
  • 2,286
  • 3
  • 34
  • 46
Mudassar Saiyed
  • 1,146
  • 10
  • 20
0

what is the datatype of like column i think it is not Integer if it is not so please ALTER the table

Manoj
  • 118
  • 12