Post reply

Minimum values

  • 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 ?

     

  • October 18, 2006 at 11:31 am

    #666597

    Similar to this thread:

    http://qa.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.

     

  • October 18, 2006 at 1:02 pm

    #666619

    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

     

     

  • October 18, 2006 at 1:20 pm

    #666625

    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

  • October 18, 2006 at 1:45 pm

    #666631

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

  • October 18, 2006 at 2:28 pm

    #666645

    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

  • October 18, 2006 at 3:39 pm

    #666670

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

    Thanks anyway.

  • October 18, 2006 at 6:42 pm

    #666688

    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)
    Intro to Tally Tables and Functions

  • October 20, 2006 at 6:06 am

    #667024

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

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

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