How to find if item is first in a column in SQLite?

Richard Plester

If I have a table:

Person:  Action:   Location:
-------  --------  ---------
Alan     Pondered  Garden
Bob      Hesitated Stairs 
Carol    ACTED     Kitchen
Dave     ACTED     Kitchen
Eric     Slept     Kitchen
Alan     ACTED     Cellar
Bob      Slept     Cellar
Fred     ACTED     Cellar

..and want to find the first person who "ACTED" first in each room, how do I do this?

I'd like to do this within an existing query:

SELECT Person,
       Location,
       <CLEVER-CODE-HERE> as flg_First --BOOLEAN was first to ACT
FROM my_table
GROUP BY Person,Location
ORDER BY Person

Desired result would be:

Person: Location: flg_First:
------- --------- ----------
Alan    Cellar    TRUE
Alan    Garden    False
Bob     Cellar    False
Bob     Stairs    False
Carol   Kitchen   TRUE
Dave    Kitchen   False
Eric    Bedroom   False
Fred    Cellar    False

I'm hoping this maybe achievable (with a window function?) but how?

forpas

With NOT EXISTS:

select m.person, m.location,
  case 
    when m.action = 'ACTED' and not exists (
      select 1 from my_table
      where action = m.action and location = m.location and rowid < m.rowid
    ) then 'TRUE'
    else 'False'
  end flg_First 
from my_table m
order by m.person, m.location

See the demo.
You can do it also with ROW_NUMBER() with a composite sorting involving action and rowid:

select person, location,
  case 
    when action = 'ACTED' 
      and row_number() over (partition by location order by action <> 'ACTED', rowid) = 1 then 'TRUE'
    else 'False'
  end flg_First
from my_table
order by person, location

See the demo.
Results:

| Person | Location | flg_First |
| ------ | -------- | --------- |
| Alan   | Cellar   | TRUE      |
| Alan   | Garden   | False     |
| Bob    | Cellar   | False     |
| Bob    | Stairs   | False     |
| Carol  | Kitchen  | TRUE      |
| Dave   | Kitchen  | False     |
| Eric   | Kitchen  | False     |
| Fred   | Cellar   | False     |

Collected from the Internet

Please contact [email protected] to delete if infringement.