sqlsql-servergroup-bymaxmin

Select all value except min value and add corresponding min value in another column

Consider this table:

id text
101 Black
102 Black
103 White
104 White
105 White

How one could select all the ids except the min id, but also add the corresponding min value in another column? So the expected result would look like:

not min text min id
102 Black 101
104 White 103
105 White 103

I'm using SQL Server 2019 (v15.x).

Solution

  • You may use windowed MIN:

    SELECT *
FROM (  
   SELECT [id], [text], [min id] = MIN([id]) OVER (PARTITION BY [text] ORDER BY [id])
   FROM Data
) t
WHERE [id] <> [min id]