CODEWITHSUNDEEP
sql
user3600124
user3600124

Reputation: 883

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

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

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269473

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.

Upvotes: 1

Related Questions