CODEWITHSUNDEEP
phpmysql
user3247433
user3247433

Reputation: 1

I want to ORDER BY the nearest number

database name:property
column name  :details
row 1        :house 5000
row 2        :land 25000
row 3        :bungalow 10000
row 4        :shop 15000
row 5        :godown 20000
row 6        :30000
row 7        :40000

the column has contains text and number. i want to sort them by the nearest number. for example if i want sort by $number=10000. the result i need like this below.

1) bungalow 10000
2) shop 15000
3) house 5000
4) godown 20000
5) land 25000
6) 30000
7) 40000

any one can help me some code like this.

$sql="select * from property ORDER BY abs(details-$number)";

Upvotes: 0

Views: 124

Answers (2)

peterm
peterm

Reputation: 92785

You can do this

SELECT *
  FROM property
 ORDER BY ABS(SUBSTRING_INDEX(details, ' ', -1) * 1 - 10000), details

Output:

|        DETAILS |
|----------------|
| bungalow 10000 |
|     house 5000 |
|     shop 15000 |
|   godown 20000 |
|     land 25000 |
|          30000 |
|          40000 |

Here is SQLFiddle demo

In php it will look like

$sql = "SELECT *
  FROM property
 ORDER BY ABS(SUBSTRING_INDEX(details, ' ', -1) * 1 - $number), details";

Note: consider to use prepared statements instead of interpolating query strings.

Upvotes: 1

Wes
Wes

Reputation: 6585

Since this is a mysql question and you want the answer in pure SQL, you first want a proper table. I would start with the following:

mysql> show create table property;
...
CREATE TABLE `property` (
  `type` varchar(250) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
)

Now let's create the data:

mysql> insert into property values ('home', 5000);
mysql> insert into property values ('land', 25000);
mysql> insert into property values ('bungalow', 10000);
mysql> insert into property values ('shop', 15000);
mysql> insert into property values ('godown', 20000);
mysql> insert into property values (NULL, 30000);
mysql> insert into property values (NULL, 40000);

Now let's define the variable we use in mysql to represent the origin:

mysql> set @origin = 10000;
Query OK, 0 rows affected (0.00 sec)

You can write your pure sql statement as such:

mysql> select *, abs(value - @origin) distance
         from property
     order by 3 asc, 2 desc;
+----------+-------+----------+
| type     | value | distance |
+----------+-------+----------+
| bungalow | 10000 |        0 |
| shop     | 15000 |     5000 |
| home     |  5000 |     5000 |
| godown   | 20000 |    10000 |
| land     | 25000 |    15000 |
| NULL     | 30000 |    20000 |
| NULL     | 40000 |    30000 |
+----------+-------+----------+
7 rows in set (0.00 sec)

Upvotes: 0

Related Questions