SQL：根据另一列的最小值提取行ID

I can't seem to get this one. So I have a table like this:

我似乎无法得到这个。所以我有一个这样的表:

RowID    UserID  Type    Data
1        A       A       1
2        A       A       2
3        A       B       1
4        A       B       2
5        B       A       1
6        B       A       2
7        B       B       1
8        B       B       2

And I need to group this table by UserID and Type and then return the RowID for the record in each group that holds the MIN value in the Data column.

我需要按UserID和Type对此表进行分组,然后返回每个组中记录的RowID,该组在数据列中保存MIN值。

So for my result set would be: 1 3 5 7

因此,对于我的结果集将是:1 3 5 7

1 个解决方案

#1

For SQL Server >= 2005, you can do:

对于SQL Server> = 2005,您可以执行以下操作:

select RowID
from (
    select RowID,
        Rank() over (Partition BY UserID, Type 
            order by Data) as Rank
    from MyTable
) tmp
where Rank = 1

SQL Fiddle Example

SQL小提琴示例

For SQL Server < 2005, you can do:

对于SQL Server <2005,您可以执行以下操作:

select t.RowID
from MyTable t
inner join (
    select UserID, Type, min(Data) as MinData
    from MyTable
    group by UserID, Type 
) tm on t.UserID = tm.UserID and t.Type = tm.Type
    and t.Data = tm.MinData

SQL Fiddle Example

SQL小提琴示例