Query code for Row Minimum?

Question

Query code for Row Minimum?

Braino

Valued Member

Points: 61
More actions
July 17, 2014 at 10:56 am

#310008

I have a name column, and then about 5 value columns. I need a new column that pulls the Minimum value for the respective row. How?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Braino (7/17/2014)
I have a name column, and then about 5 value columns. I need a new column that pulls the Minimum value for the respective row. How?

This gets asked around here a lot.

Here a few one of these threads...

http://www.sqlservercentral.com/Forums/Topic722418-338-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Karthiart Ten Centuries Points: 1070 More actions · Answer 2

Hi,

Hope this helps you.

A request, while posting your doubts. Please come up with your neccessary scripts say, create table, sample values. So that our guys will help you as soon as possible. Thanks for understanding.

create table #Test(Name varchar(40), V1 INT,V2 INT,V3 INT,V4 INT,V5 Varchar(40))

INSERT INTO #Test VALUES('Kumar',1,4,6,3,'')

INSERT INTO #Test VALUES('Karthik',11,4,16,3,'')

INSERT INTO #Test VALUES('Rajesh',12,24,216,53,'')

select * from #test

--Taking the min value for each name

Update #Test

set v5 = B.MinValue

from #Test

inner join

(select Name,min(v1) as MinValue from (

select Name,v1 from #Test

union select Name,v2 from #Test

union select Name,v3 from #Test

union select Name,v4

from #Test)A

group by Name)B

ON #Test.Name = B.Name

select * from #test

Drop table #TEST

Regards,

Karthik.

Regards,
Karthik.
SQL Developer.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Karthik posted some excellent code that will do what you are asking. However, I would suggest that in your case you don't do it like this. The minimum across a set of columns is not static. Once you update a row you also need to update column5. I would suggest that instead you make column5 a computed column. You will need to create a function for this and then you can use that function in your computed column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Braino Valued Member Points: 61 More actions · Answer 4

I ended up posting to an excel spreadsheet and using sheetformulas to get the data quickly and get me out of a jam, but im a newbie at SQL code and both answers helped my understand SQL a little bit more. Thanks for the help, both of you.