But my favourite would probably be this:

DECLARE @SampleData

TABLE (

id INTEGER NOT NULL,

first INTEGER NOT NULL,

sec INTEGER NOT NULL,

group_id INTEGER NOT NULL,

vid INTEGER NOT NULL,

computed AS ISNULL(first * sec, 0)

PRIMARY KEY CLUSTERED (id)

);

INSERT @SampleData

VALUES (1, 1, 2, 16, 122),

(3, 2, 1, 16, 122),

(2, 3, 1, 08, 033),

(4, 1, 5, 16, 012),

(5, 1, 6, 16, 001);

SELECT S.id, S.group_id, S.computed

FROM @SampleData S

WHERE S.computed =

(

SELECT MIN(S2.computed)

FROM @SampleData S2

WHERE S2.group_id = S.group_id

);

The computed column in the sample table is required to make this work best, but it does not need to be persisted or indexed, so it only really exists in the metadata.

This is a workaround for a limitation in the query optimiser, which might not be necessary in future versions.

One benefit of the computed column (aside from needing no storage space) is that you can create statistics on it. Or at least you could on a real table - the table variable I used for the demo does not support statistics.