SQLite How to Group data according to custom rules

user_14258508 0
Viewed 94

I have a table as below. If I do a group by operation on the name field, the key of b is 11,but what I need to leave is 12, because 12 has already appeared in other records. What should I do to achieve this result, without using the max aggregation method

Introduce the meaning of the table, key-12 also provides name-a, name-b, key-11 only provides name-b For name-c, there are three key that can provide name-c, all of which are not repeated

|name|key|
| a | 12 |
| b | 11 |
| b | 12 |
| c | 15 |
| c | 14 |
| c | 17 |
....

What I hope is that the result obtained through group by is:

|name|key |
| a  | 12 |
| b  | 12 |
| c  | 15 |

To perform group by operations through the name field, b needs to leave key-12, because key-12 provides both name-a and name-b, so key-11 is not needed.

For name-c, there are three key that can provide name-c, all of which are not repeated, so let's just use the one that appears for the first time.

window-functions count sqlite sql python
1 Answers

Assuming that key is unique for each name you can use COUNT() and ROW_NUMBER() window functions:

select name, key
from (
  select *, row_number() over (partition by name order by counter desc, rowid) rn
  from (
    select *, rowid, count(*) over (partition by key) counter
    from tablename
  )
) 
where rn = 1

See the demo.
Results:

> name | key
> :--- | --:
> a    |  12
> b    |  12
> c    |  15
user_104988280
Related