CODEWITHSUNDEEP
sqlite
Michael
Michael

Reputation: 9402

SQL to find last sibling in a linked list

I've got a simple table that has a previous field for each row so as to create a linked list. I am trying to find a query that will find the id of the last element in the list. My first thought was that perhaps I could use some sort of self join, but I'm not sure how to express that what I am looking for is the id of the element which does not appear as the "previous" field in some other row.

My second thought was perhaps a query that uses "not in" and the list of all ids (e.g. SELECT id) but I'm not sure how to attach that a particular field (e.g. previous). In other words, I would want something like:

SELECT * FROM test WHERE (SELECT id) NOT IN previous

But the SQL code is expecting previous to be a table, whereas I want to find where non of the ids are in the previous value.

If I try the reverse, for some reason it matches all rows:

SELECT * FROM test WHERE previous NOT IN (SELECT id)

Upvotes: 0

Views: 259

Answers (1)

C3roe
C3roe

Reputation: 96382

As for the second thought:

SELECT id FROM foo f1 WHERE id NOT IN (SELECT previous FROM foo f2)

Upvotes: 1

Related Questions