Select min group by another column

Question

I have table consisting of these fields:

id  |   GroupId | position      
 ------------------------------------
 CK1         10       1
 CK1         10       2    
 CK1         10       3    
 CK1         11       4    
 CK1         11       5    
 CK1         11       6    
 CK1         12       7    
 CK1         12       8    
 CK1         12       9 

I need a select that includes the min value of position in the group of the registre:

id  |   GroupId | position   | MinPosition         
 -------------------------------------------
 CK1         10       1             1
 CK1         10       2             1    
 CK1         10       3             1    
 CK1         11       4             4
 CK1         11       5             4
 CK1         11       6             4
 CK1         12       7             7
 CK1         12       8             7
 CK1         12       9             7

Thanks

Answer 1

declare @t table (id varchar(5),Groupid int)
insert into @t (id,Groupid)values ('CK1',10),
('CK1',10),
('CK1',10),
('CK1',11),
('CK1',11),
('CK1',11),
('CK1',12),
('CK1',12),
('CK1',12),
('CK1',12)

select id,Groupid,RANK()OVER( ORDER BY GROUPID)MINPosition
 from @t

Answer 2

You can use this in the most DBMS:

SELECT id, groupid, position, 
FROM myTable as mt
INNER JOIN (
        SELECT id, groupid, MIN(position)
        FROM myTable 
        GROUP BY id, groupid
    ) as mini
    ON mt.id = mini.id
    AND mt.groupid = mt.groupid

This is the better version if you use SQL Server (>= 2008)

SELECT id, groupid, position, MIN(position) OVER (PARTITION BY id, groupid)
FROM myTable as mt

Here a full code demo:

CREATE TABLE #temp(id char(3), groupid int, position int)

INSERT INTO #temp(id, groupid, position)
VALUES (N'CK1',10,1),(N'CK1',10,2),(N'CK1',10,3),(N'CK1',11,4),(N'CK1',11,5),(N'CK1',11,6),(N'CK1',12,7),(N'CK1',12,8),(N'CK1',12,9) 

SELECT id, groupid, position, MIN(position) OVER (PARTITION BY id, groupid)  as MinPosition
FROM #temp as mt

DROP TABLE #temp

This produces this result:

id   groupid     position    MinPosition
---- ----------- ----------- -----------
CK1  10          1           1
CK1  10          2           1
CK1  10          3           1
CK1  11          4           4
CK1  11          5           4
CK1  11          6           4
CK1  12          7           7
CK1  12          8           7
CK1  12          9           7