SELECT last row, but only if column value is the expected one

user_3600124 0
Viewed 26

My Table

---------------------
id | userid | name
---------------------
0     1       John
1     2       Ellen
2     1       Peter
---------------------

This table shows, that John has changed his name to Peter (same userid - same person) as id=2 is newer record than id=0

Now... I may want to check whether John is the last name of userid=1. I need to SELECT id of the LAST row WHERE userid=1, but only if name=John. And since name in the last row with userid=1 is not John in this particular case, NULL should be returned. How do I do that? With single SQL command. Thanks

I am using Sqlite3 by the way, some advanced MySQL stuff may not work in my case

sql
1 Answers

Well, one method is to use order by and limit the results to one row:

select (case when name = 'John' then name end)
from t
where userid = 1
order by id desc
fetch first 1 row only;

This uses the fetch first clause because it is Standard SQL. Your database might have another method.

user_11440350
Related