Can select next record alphabetically, but what happens when the record name is identical?

With this sql I can grab the next name in alphabetical order using the ID of

SELECT id 
FROM `names` 
WHERE `name` > (SELECT `name` FROM `names` WHERE `id` = X) 
ORDER BY `name` ASC, `id` ASC

However let's asume I have these I have these records

id | name
---------
12 | Alex
8  | Bert
13 | Bert
17 | Bert
4  | Chris

Say I have id 12 as reference I get the results

id | name
---------
8  | Bert
13 | Bert
17 | Bert
4  | Chris

But if I use 8 as reference I get

id | name
---------
4  | Chris

Bert 13 and 17 would ge开发者_Go百科t skipped.

This may seem obvious but if you wanted to order by 2 fields such as first and last names then you will need to concatenate (CONCAT / CONCAT_WS) the first and last name fields in order to find the previous or next result. If you have names that are the same then you may find yourself looping from one name to the other and back again, to prevent this, concatenate the ID of the row to the end of the concatenated first and last name. This will work better than testing if the id is greater than the current id (OR n.names = q.name AND n.id > q.id) as if the names have not been inserted alphabetically then you will miss sections of results (not what is wanted when browsing to the next / previous result). Hope this helps someone.

Try a condition like this:

WHERE `name` > (SELECT `name` FROM `names` WHERE `id` = X)
      OR `name` = (SELECT `name` FROM `names` WHERE `id` = X) AND `id` > X

That's because you're comparing names with the greater than operator, which will exclude any names which are equal to. If you want to keep respecting the id:

SELECT n.id 
FROM names n
JOIN (SELECT name, id FROM names WHERE id = X) q
  ON n.id = q.id
WHERE n.name > q.name
   (OR n.names = q.name AND n.id > q.id)
ORDER BY n.name ASC, n.id ASC

Here we use the inner query to return not only names, but also corresponding id's. We can then use the id as a tie-breaker in the case of equal names.