All Forums Database
mithunk 7 posts Joined 02/13
31 Mar 2013
selecting values from a table where max value is null

I have to select rows from the table where col1=max(col1).
Here if the max(col1) is null, i dont get results because NULL cannot be compared as far as i know.
Temporarily i have used COALESCE(COL1,CURRENT_DATE)= COALESCE(MAX(COL1),CURRENT_DATE) to fetch records.
Will this temp solution affect in terms of performance ?
I have also tried creating a volatile table with these coalesce statements then used those columns to filter. Which one is better using ?
Please suggest if there is any other means to acheive.
Scenario:
CREATE VOLATILE TABLE tbl1
( name VARCHAR(10),
dt DATE
)ON COMMIT PRESERVE ROWS;
INSERT INTO tbl1
('mk', NULL);
INSERT INTO tbl1
('jk', NULL)
SEL * FROM tbl1 -- 2 inserted records
SEL * FROM tbl1
WHERE dt=(SEL MAX(dt) FROM tbl1);
--No records
SEL * FROM tbl1
WHERE COALESCE(dt,CURRENT_DATE)=(SEL COALESCE(MAX(dt),CURRENT_DATE) FROM tbl1);
--2 Records from the table
Thanks in advance.
 

Mithun
KS42982 137 posts Joined 12/12
01 Apr 2013

One another option is to use CASE WHEN.
To know what would work better, you can check EXPLAIN and compare the performance of different options.

Adeel Chaudhry 773 posts Joined 04/08
03 Apr 2013

Selecting with COALESCE(COL1,CURRENT_DATE)= COALESCE(MAX(COL1),CURRENT_DATE) is best option.

-- If you are stuck at something .... consider it an opportunity to think anew.

mithunk 7 posts Joined 02/13
03 Apr 2013

So, is the volatile table option compartively less effective than the COALESCE Option in the filter ?
 

Mithun

Makuma 2 posts Joined 09/12
10 Apr 2013

If you really want the rows where max(dt) is null, you can simply use this, isnt it?
 
 

SEL NAME,MAX(dt)  mx FROM tbl1

group by 1

HAVING MX IS NULL

You must sign in to leave a comment.