SQL Problem

1

I have a table defined as

 "CREATE TABLE \(tableNAME) ("
        + " maingroup        Text    NOT NULL COLLATE NOCASE,"
        + " subGroup         Text    NOT NULL COLLATE NOCASE,"
        + " levelFile        Text    NOT NULL COLLATE NOCASE,"
        + " levelName        Text    NOT NULL COLLATE NOCASE,"
        + " locked           Boolean NOT NULL DEFAULT true,"
        + " solved           Boolean NOT NULL DEFAULT false,"
        + "PRIMARY KEY (maingroup,subgroup,levelfile))"

I need to set “locked” to true for all but the first 4 records of each (maingroup,subgroup) those would be set to false. Unfortuantly the levelname values are not consistent enough to be used for this purpose

2

what do you mean by “first four”?
this should be doable with a combination of ORDER BY and LIMIT

3

there are like 20 combinations of maingroup/subgroup so Limit won’t work

4

You didn’t say which DB, but if it has row_number and window functions it can be done.

5

SQLite… but I solved it another way… turns out I needed an index number for each group anyways… so it was just “where index<=4”

Thanks

1 Like