i have a table like this:
| kind | basket | is_fruit |
|---|---|---|
| banana | 1 | 1 |
| strawberry | 1 | 1 |
| pineapple | 1 | 1 |
| orange | 2 | 1 |
| battery | 2 | 0 |
| hat | 2 | 0 |
| ... | ... | ... |
| berry | 9 | 1 |
| cherry | 10 | 1 |
i want to organize the records by 2 elements, according to basket(if they are in the same or close baskets) AND is_fruit(they have to be fruits in the same basket and other items in another basket ). I tryied using dense_rank but it doesn't work because I want a specific number of rows in a group.
example output:
| rank | kind | basket | is_fruit |
|---|---|---|---|
| 1 | banana | 1 | 1 |
| 1 | strawberry | 1 | 1 |
| 2 | pineapple | 1 | 1 |
| 2 | orange | 2 | 1 |
| 3 | battery | 2 | 0 |
| 3 | hat | 2 | 0 |
| ... | ... | ... | ... |
| 7 | berry | 9 | 1 |
| 7 | cherry | 10 | 1 |