CODEWITHSUNDEEP
sqlsqliteselect
Richard Plester
Richard Plester

Reputation: 1218

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

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?

Upvotes: 0

Views: 39

Answers (2)

forpas
forpas

Reputation: 164154

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     |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Let me assume that you have an ordering column, such as rowid, that specifies the ordering of the rows.

If so, one method uses row_number() with some conditional logic:

select t.*,
       (case when action <> 'ACTED' then 'false'
             when 1 = row_number() over (partition by location, action order by rowid)
             then 'true' 
             else 'false'
        end) as flag
from t;

Upvotes: 2

Related Questions