CODEWITHSUNDEEP
mysql
Marcus Mansur
Marcus Mansur

Reputation: 337

mysql: order by nearest id

i have a query that returns some users related to a specific user (Bob).

I need to retrieve the nearest records, meaning, i must return users whose ID column is near Bob's ID.

For example:

         ID

Tom      5
Mike     8
Bob      10
Jack     12
Brian    13

The query:

SELECT users.* FROM users
INNER JOIN neighboors on neighboors.neighboor_id = users.id #ignore this join, just to exemplify
WHERE neighboors.user_id = 10 # bobs id
ORDER BY something
LIMIT 3 # i want to return only the 3 nearest users (according to the table above:mike, jack and brian)

How can i achieve this?

updated

the logic is, users can plant trees, each tree has an specie. The query should return users that have planted the same tree specie.

And why is important order by proximity of id? the client want this way :) there is no other reason.

Upvotes: 2

Views: 658

Answers (3)

aleroot
aleroot

Reputation: 72636

Try with this, should do what you need : 

SELECT users.* FROM users
INNER JOIN neighboors ON neighboors.neighboor_id = users.id
WHERE neighboors.user_id = 10 
ORDER BY ABS(neighboors.user_id - 10)
LIMIT 3

The ABS function in this case it is used to calculate the "distance" from user_id selected value (the value filtered by the WHERE ... ).

To obtain better performance on large tables you have to index(if not yet) the column : neighboors.user_id .

Upvotes: 5

Alex
Alex

Reputation: 186

The problem is that nearness works in both a positive and negative direction.

If you had:

Tom      5
Mike     8
Sally    9
Bob      10
Sarah    11
Jack     12
Brian    13

Then do you want to return Mike, Sally and Sarah, or Sally, Sarah and Jack? Do you prefer ascending proximity or descending proximity?

It will help to know exactly what business logic this is trying to implement. Why is it important to select by proximity of the ID? How does the ID relate users to each other?

I'd be interested in helping if you can provide more details.

Upvotes: 0

Chetter Hummin
Chetter Hummin

Reputation: 6817

One way to do this is to store the differences as a separate column in an inner query and then query for the smallest differences. A good example for nested queries is at : http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

Upvotes: 1

Related Questions