CODEWITHSUNDEEP
sql-servert-sqlgroup-by
Morvael
Morvael

Reputation: 3567

T-SQL select rows where [col] = MIN([col])

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

Answers (2)

Tyron78
Tyron78

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

Giorgos Betsos
Giorgos Betsos

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

Demo here

Upvotes: 1

Related Questions