Given a table such as
| id | name | city | priority | updated |
|---|---|---|---|---|
| 1 | isaac | mars | 1 | TIMESTAMP |
| 2 | asimov | paris | 0 | TIMESTAMP |
| 3 | jake | york | 1 | TIMESTAMP |
I want to fetch a number of items, 1 per city/name combination, and prioritize/ensure that rows with priority are included in the result. After handling priority the “oldest” row would be selected
For example, in the table above, it would be expected to fetch ids 1 and 3 if we fetch 2 rows. If we fetch 3 rows instead of 2, then id 2 would be included as well (we start by getting priority rows, and fill out with non priority rows if we have room)
Im trying with a query like
select any_value(id) keep(dense_rank first order by priority, updated) from table group by city, name fetch next 2 rows only
but have trouble with the priority aspect. A possible solution could be to go for a join of two different selects, one to get priority rows and one to get non priority (given bandwidth) but that seems like fairly convoluted and inefficient approach and would probably not scale well as the database grow