selecting values from a table where max value is null

Database
Enthusiast

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.

Tags (2)
4 REPLIES
Enthusiast

Re: selecting values from a table where max value is null

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.

Teradata Employee

Re: selecting values from a table where max value is null

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

Enthusiast

Re: selecting values from a table where max value is null

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

Fan

Re: selecting values from a table where max value is null

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