Reputation: 3567
I have a data set produced from a UNION
query that aggregates data from 2 sources.
I want to select that data based on whether or not data was found in only of those sources,or both.
The data relevant parts of the set looks like this, there are a number of other columns:
row | preference | group | position |
---|---|---|---|
1 | 1 | 111 | 1 |
2 | 1 | 111 | 2 |
3 | 1 | 111 | 3 |
4 | 1 | 135 | 1 |
5 | 1 | 135 | 2 |
6 | 1 | 135 | 3 |
7 | 2 | 111 | 1 |
8 | 2 | 135 | 1 |
The [preference]
column combined with the [group]
column is what I'm trying to filter on, I want to return all the rows that have the same [preference]
as the MIN([preference])
for each [group]
The desired output given the data above would be rows 1 -> 6
The [preference]
column indicates the original source of the data in the UNION
query so a legitimate data set could look like:
row | preference | group | position |
---|---|---|---|
1 | 1 | 111 | 1 |
2 | 1 | 111 | 2 |
3 | 1 | 111 | 3 |
4 | 2 | 111 | 1 |
5 | 2 | 135 | 1 |
In which case the desired output would be rows 1,2,3, & 5
What I can't work out is how to do (not real code):
SELECT * WHERE [preference] = MIN([preference]) PARTITION BY [group]
Upvotes: 0
Views: 73
Reputation: 4197
Should by doable via simple inner join:
SELECT t1.*
FROM t AS t1
INNER JOIN (SELECT [group], MIN(preference) AS preference
FROM t
GROUP BY [group]
) t2 ON t1.[group] = t2.[group]
AND t1.preference = t2.preference
Upvotes: 0
Reputation: 72235
One way to do this is using RANK
:
SELECT row
, preference
, [group]
, position
FROM (
SELECT row
, preference
, [group]
, position
, RANK() OVER (PARTITION BY [group] ORDER BY preference) AS seq
FROM t) t2
WHERE seq = 1
Upvotes: 1