Haminteu Haminteu - 11 days ago 5
SQL Question

Add Min Value on Query Output in Separate Column

I have the following table:


No Item Value
----------------------------
1 A 5
2 B 8
3 C 9


If I use Min function on
Value
field, then I'll get
5
.


My question is, how can I put the MIN value into a new column? Like the following result:


No Item Value newCol
----------------------------
1 A 5 5
2 B 8 5
3 C 9 5


Is it possible to do that?

Thank you.

Answer

I'd prefer to do the subquery in a join, you'll have to name the field. Something like this;

Sample Data

CREATE TABLE #TestData (No int, item nvarchar(1), value int)
INSERT INTO #TestData (No, item, value)
VALUES
(1,'A',5)
,(2,'B',8)
,(3,'C',9)

Query

SELECT 
     td.No
    ,td.item
    ,td.value
    ,a.Min_Value
FROM #TestData td
CROSS JOIN
(
    SELECT
       MIN(Value) Min_Value
    FROM #TestData
) a

Result

No  item    value   Min_Value
1   A       5       5
2   B       8       5
3   C       9       5
Comments