May 22, 2009 at 2:48 pm
Hello,
consider:
;with mycols( ID, colvalue1, colvalue2, colvalue3) AS (
SELECT 1, 3, 1, 2
UNION SELECT 2, 5, 7, 4
UNION SELECT 3, 37, 9, 22
)
SELECT * FROM myCols
I need build a way to return to least value for the value columns for a particular record,
ID minvalue
1 1
2 4
3 9
Does anyone have a technique to do this? (I need a 'fast' way: not millions, but thousands of records...)
Thanks in advance,
Mark
Mark
Just a cog in the wheel.
May 22, 2009 at 3:03 pm
Try using case like this:
;
with mycols(ID, colvalue1, colvalue2, colvalue3)
AS (SELECT
1,
3,
1,
2
UNION
SELECT
2,
5,
7,
4
UNION
SELECT
3,
37,
9,
22)
SELECT
ID,
CASE WHEN colvalue1 <= colvalue2 AND
colvalue1 <= colvalue3 then colvalue1
WHEN colvalue2 <= colvalue3 THEN colvalue2
ELSE colvalue3
END,
colvalue1,
colvalue2,
colvalue3
FROM
myCols
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2009 at 3:06 pm
Look up the PIVOT operator in Books Online, I think that should do the trick for you.
A.J.
DBA with an attitude
May 22, 2009 at 3:23 pm
A.J. Wilbur (5/22/2009)
Look up the PIVOT operator in Books Online, I think that should do the trick for you.
Thanks A.J. that did it...
Jack's solution would have worked, but I didn't fully describe that I acually had 17 columns to survey...
here's my test code that works (I'll build a function to do the real dirty work:
;with mycols( ID, colvalue1, colvalue2, colvalue3) AS (
SELECT 1, 3, 1, 2
UNION SELECT 2, 5, 7, 4
UNION SELECT 3, 37, 9, 22
)
SELECT ID, [theminimumvalue] = MIN(mymin)
FROM
(SELECT ID, colvalue1, colvalue2, colvalue3
FROM myCols) AS p
UNPIVOT
(myMin FOR zCol IN
(colvalue1, colvalue2, colvalue3)
)AS unpvt
GROUP BY ID
GO
returns:
IDtheminimumvalue
11
24
39
seems to do it..
Cheers and best regards for Memorial Day,
mark
Mark
Just a cog in the wheel.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply