SQL Selecting MIN value from row data with null values

Question

My table in Oracle is like this

Col   Col1  Col2  Col3  ColR
--   ----  ----   ----  ---- 
30    73    40    null  -10
60    32    null  20     40
90    80    null  null   10
80    45    81    30     50

I can also set 0 instead of null in the above column. I need to find the min value from Col1,Col2,Col3 ignoring null or 0 and populate the ColR by subtracting from Col.

EDIT: i wrote a CASE statement which doesn't work due to the null values inside my table.

 SELECT col,col1,col2,col3,
  CASE
    WHEN Col1 < Col2 AND Col1  < Col3
    THEN Col - Col1
    WHEN Col2 < Col1 AND Col2  < Col3
    THEN Col - Col2
    ELSE Col - Col3
  END ColR
FROM
 (SELECT col,col1,
   CASE
    WHEN col22 IS NULL
    THEN NULL( i can also SET TO 0 but it will mess  WITH my other calculation TO find MIN)
      ELSE ROUND( 100* ( (col22) / (col44)))
    END col2 ,
    CASE
      WHEN col33 IS NULL
      THEN NULL
      ELSE ROUND( 100* ( (col33) / (col44)))
    END col3
  FROM TABLE
  )

I have just included the case statement inside my select query. all the the column values all populated from another query.

Also, you can at least extract the mention of col outside the case statement. — Clockwork-Muse
– Clockwork-Muse, Commented Aug 17, 2012 at 17:11

Justin Cave · Accepted Answer · 2012-08-17 17:23:28Z

2

It sounds like you want something like

SELECT least( (case when col1 is null or col1 = 0 then 999999999 else col1 end),
              (case when col2 is null or col2 = 0 then 999999999 else col2 end),
              (case when col3 is null or col3 = 0 then 999999999 else col3 end) )
  FROM <<table name>>

where 999999999 is some numeric value that is large enough that it will always be larger than any other valid value. If it is possible that all three columns will have NULL or 0 values, then you'd probably want to add an additional check that if the result of that least function is 999999999 that you return 0 or NULL or whatever else makes sense.

@X-Zero was kind enough to put together a working SQL Fiddle example of this construct. Note that his example is filtering out the rows where all three columns have either NULL or 0 values.

edited Aug 17, 2012 at 17:23

answered Aug 17, 2012 at 17:08

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Clockwork-Muse Over a year ago

I didn't know Oracle had the relevant multi-parameter minimum function (DB2 re-uses the MAX() function)! I'd add an answer, but it'd be essentially this, so have a working SQL Fiddle example instead, complete with null-row ignoring.

Justin Cave Over a year ago

@X-Zero - Thanks for the example! I added a link to it in my answer.

Dhruvenkumar Shah · Accepted Answer · 2012-08-17 16:50:02Z

1

// IF YOU NEED MINIMAL FROM COL1 or COL (ANY COLUMN)

SELECT MIN (COL1) FROM (SELECT * FROM TABLE WHERE COL1 IS NOT NULL)

Can you please elaborate I am not able to help you with this small set of info actually.

answered Aug 17, 2012 at 16:50

Dhruvenkumar Shah

5282 gold badges10 silver badges27 bronze badges

Comments

mikeY · Accepted Answer · 2012-08-17 17:04:21Z

1

Oracle NVL Usage:

nvl(check_expression, replacement_value)

So nvl(col2,0) ought to take of nulls that mess with your math.

So try:

CASE
       WHEN nvl(col1,0) < nvl(col2,0) AND nvl(col1,0) < nvl(col3,0)
       THEN Col - nvl(col1,0)
       WHEN  nvl(col2,0) < nvl(col1,0) AND nvl(col2,0) < nvl(col3,0)
       THEN Col - nvl(col2,0)
       ELSE Col - nvl(col3,0)
END ColR

EDIT: Taking X-Zero's point which I missed. I think if you replace the NULLS with 9999999 instead of 0, the logic will work, although that may be too specific to this sample data and not a real world solution.

edited Aug 17, 2012 at 17:04

answered Aug 17, 2012 at 16:58

mikeY

5294 silver badges14 bronze badges

6 Comments

Clockwork-Muse Over a year ago

Except he wants to ignore nulls/0

Tony Hopkinson Over a year ago

As long as none of col1 to col3 could be negative. :(

Tony Hopkinson Over a year ago

If that's what the OP is saying then @Cody seems to have a goer

jero Over a year ago

i have modified the query, i want to ignore even 0

mikeY Over a year ago

@ Tony Hopkinson - Yes, I think Cody and I may have arrived at the 999999 thing simultaneously by different paths.

|

Cody · Accepted Answer · 2012-08-17 17:05:54Z

0

If you want to ignore nulls in a column, you could wrap them with the NVL function. This replaces null values in a column with the value specified, for which you could use some large number. For example:
NVL(col1,99999)

Oracle Database SQL Reference - NVL: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

answered Aug 17, 2012 at 17:05

Cody

2,4802 gold badges22 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQL Selecting MIN value from row data with null values

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related