CODEWITHSUNDEEP
sqlsql-servergroup-bymaxmin
nzskra
nzskra

Reputation: 183

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).

Upvotes: 0

Views: 64

Answers (3)

Milad Hamid Elhaei Sahar
Milad Hamid Elhaei Sahar

Reputation: 583

WITH MinIDs AS
        (
                SELECT
                        MIN(id) AS MinID,
                        text
                FROM
                        TableName
                GROUP BY
                        text )
SELECT
        t.id AS [not min],
        t.text           ,
        m.MinID AS [min id]
FROM
        TableName t
INNER JOIN
        MinIDs m
ON
        t.text = m.text
WHERE
        t.id != m.MinID

Upvotes: 0

Shivam
Shivam

Reputation: 1

You can try using subquery with join.

SELECT 
    T1.id 'not min', 
    T1.color, 
    T2.min_id 
FROM 
    TEST T1
JOIN 
    (SELECT color, MIN(id) min_id FROM TEST GROUP BY color) T2 
    ON T1.color = T2.color
WHERE 
    T1.id != T2.min_id;

Upvotes: 0

Zhorov
Zhorov

Reputation: 30003

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]

Upvotes: 2

Related Questions