Minimum values

Question

Post reply

Minimum values

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
October 18, 2006 at 11:15 am

#115142

I have a table with 4 columns. I need to get the minimum nonzero value of the 4 columns for each row.
Row #
ColA
ColB
ColC
ColD
1
5
10
12
0
2
4
2
12
15
3
6
3
0
5
It should return
Row #
MinVal
1
5
2
2
3
3
Is there a function to return this output ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Similar to this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=315718

You will need to use CASE to make zero return NULL.

ps You may do better if the data is normalized.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

Try this

DECLARE @tbl TABLE

(RowId INT IDENTITY(1,1),

Col1 INT,

Col2 INT,

Col3 INT,

Col4 INT)

INSERT INTO @tbl VALUES(5,10,12,0)

INSERT INTO @tbl VALUES(4,2,12,15)

INSERT INTO @tbl VALUES(6,3,0,5)

SELECT RowId,

MinValues=

(SELECT MIN(D1) FROM

(SELECT CASE WHEN Col1 = 0 THEN NULL ELSE Col1 END AS D1

UNION

SELECT CASE WHEN Col2 = 0 THEN NULL ELSE Col2 END AS D1

UNION

SELECT CASE WHEN Col3 = 0 THEN NULL ELSE Col3 END AS D1

UNION

SELECT CASE WHEN Col4 = 0 THEN NULL ELSE Col4 END AS D1 ) AS MinValues)

FROM @tbl

Ram

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Wrong table design again.

People, relational database IS NOT EXCEL SPREADSHEET!!!

Table must look like this:

Row# DataRange DatValue

1 ColA 5

1 ColB 10

1 ColB 12

1 ColD 0

2 ColA 4

2 ColB 2

2 ColB 12

2 ColD 15

3 ColA 6

3 ColB 3

3 ColB 0

3 ColD 5

Then you don't have stupid problems with aggregate queries.

_____________
Code for TallyGenerator

Ram Ram Ten Centuries Points: 1079 More actions · Answer 4

If some body has questions and gets stranded, please try to help them out and then start giving big big lectures!

Sergiy SSC Guru Points: 110208 More actions · Answer 5

If some body has depression and gonna commit suicide, please try to help them out and then start giving big big lectures!

_____________
Code for TallyGenerator

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 6

This is not an actual table but a query. For simplicity sake, I had given the output as a table.

Thanks anyway.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Junkie,

Would you post the query that creates the example output that you posted? I think that's probably the key, here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stuart Kinnear Valued Member Points: 67 More actions · Answer 8

You could change the value of zero to null using NULLIF then use the MIN function (which ignores null)