CODEWITHSUNDEEP
mysql
Chud37
Chud37

Reputation: 5007

Get before and after rows SQL

I have the following SQL:

SELECT `PersonID`, `Title`.`Title`, `FirstName`, `LastName` 
FROM `Person` 
ORDER BY `cft` ASC, `FirstName` ASC

Now the problem is because the results are not numeric, Is there way to get the before and after rows if I set the PersonID to be say 1020?

Upvotes: 0

Views: 62

Answers (1)

zerkms
zerkms

Reputation: 254916

Here is a condition for the next row (assuming there are no similar FirstName for each cft):

WHERE (`cft` = :cft AND FirstName > :firstname) OR (`cft` > :cft)
ORDER BY `cft` ASC, `FirstName` ASC
LIMIT 1

where :cft and :firstname are the correspondent values from the row with PersonID = 1020.

Condition for the previous row is pretty similar to the one I've shown and I'm sure you could get it yourself (treat it as a homework)

Upvotes: 1

Related Questions