I am quite new to SQL, and I am trying to understand, how could I get the next user from a table in alphabetical order when the table has duplicate last names? Currently, I have managed to go through the list in alphabetical order, but it will skip duplicate names or get stuck in a forever loop.

Any suggestions what I should do / look into to fix the logic in my SQL query?

ID > ID - will skip an user if their ID is lower:

SELECT formID FROM table 
WHERE formID > ? AND 
lastName >= (SELECT lastName FROM table WHERE formID = ?) ORDER BY lastName ASC"

ID <> ID will cause a forever loop if same last name:

SELECT formID FROM table 
WHERE formID <> ? AND lastName >= (SELECT lastName FROM table WHERE formID = ?) 
ORDER BY lastName ASC"

EDIT: Seems that I managed to find a solution to my issue by modifying my SQL code a bit. Though I am not quite sure if using LIMIT 1 has any changes to my query.

SELECT formID FROM table 
WHERE 
  lastName > (SELECT lastName FROM table WHERE formID = ? ) 
              OR lastName = (SELECT lastName FROM table 
              WHERE formID = ?) 
AND formID <> ? ORDER BY lastName ASC LIMIT 1"

CodePudding user response：

For this task you could try using a ranking window function among RANK, DENSE_RANK and ROW_NUMBER. These functions allow you to craft a new field containing a ranking value for each row, according to the order of your choice.

Once you have this rank number, and given a new last_name value, you can get the corresponding next user by examining the current rank and getting the 'rank 1' row, without needing to worry on users with the same name, as they will have different rank.