CODEWITHSUNDEEP
phpmysqlselectnumberssql-order-by
Akos
Akos

Reputation: 2007

Mysql ORDER BY numbers DESC

I have a simple mysql select query in my PHP code:

$result = mysql_query("SELECT text FROM example ORDER BY rank DESC");
while($row = mysql_fetch_array($result))
  {
  echo $row['text'] . "<br>";
  }

and this MySql table:

   text | rank
--------+--------
 google | 245
--------+--------
 yahoo  | 32
--------+--------
 bing   | 12

When I get the results from the query, something like this gets displayed:

yahoo 
google
bing

I want Google to be in front. I guess Yahoo is in first because it starts with "3".

How could I make the query order the results by the size of the numbers in rank?

Thanks...

Upvotes: 4

Views: 19294

Answers (5)

SB24
SB24

Reputation: 531

$result = mysql_query("SELECT * FROM `example` ORDER BY `rank` DESC");

while($row = mysql_fetch_array($result))
{
  echo $row['text'] ."<br>";
}

Upvotes: 1

Dimitre Radoulov
Dimitre Radoulov

Reputation: 28000

The correct solution, of course, is to use the correct data type. As a workaround you could cast the data to number on the fly:

SELECT text FROM example ORDER BY rank + 0 DESC

or:

SELECT text FROM example ORDER BY cast(rank as unsigned) DESC

Upvotes: 7

Nick Rolando
Nick Rolando

Reputation: 26167

I'm guessing the rank field is some kind of string type. Make it a numeric type int and it will order properly

Upvotes: 9

bcmcfc
bcmcfc

Reputation: 26765

What's the data type of rank in your SQL schema? Set it to a numeric type.

Upvotes: 3

Naftali
Naftali

Reputation: 146310

Try this:

$result = mysql_query("SELECT * FROM example ORDER BY rank DESC");
while($row = mysql_fetch_array($result))
{
  echo $row['text'] . "  ". $row['rank'] ."<br>";
}

And see if the ranks are being sorted correctly.

Upvotes: 1

Related Questions