Check if values in a column of one set match values in another set
0
votes
2
answers
157
views
Given two results consisting of single key|value pair (coming from CTEs), I want to join and group them by key, aggregate their values and return two different things:
a) those keys where aggregated list of values in first resultset exactly matches aggregated list of values in second resultset
b) those keys where aggregated list of values in first resultset matches second resultset independent of order
I know of
string_agg()
, but it seems I can use it in the SELECT
list, and it's inefficient anyway. Is there something more efficient?
Set 1
|key | value |
|-----|-------|
| 1 | 1 |
| 1 | 2 |
| 3 | 4 |
| 2 | 5 |
| 2 | 7 |
| 1 | 3 |
Set 2
|key | value |
|-----|-------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 7 |
| 2 | 5 |
| 4 | 6 |
Desired result:
a) key 1
(1,2,3 = 1,2,3)
b) key 1 and key 2
(5,7 = 7,5)
Asked by gadelat
(101 rep)
May 10, 2018, 12:23 AM
Last activity: Jul 19, 2025, 11:48 PM
Last activity: Jul 19, 2025, 11:48 PM