CODEWITHSUNDEEP
sqlsql-serversqlitejoin
Dester Dezzods
Dester Dezzods

Reputation: 1545

Simple SQL query to select a max

I need a small help with a SQL query.

I have two tables: tbltrans and tbltrans_temp. I want to select the maximum of tbltrans_temp max(tbltrans_temp.transid).

If tbltrans_temp is empty and it returns null, it should then take the max of tbltrans.transid.

If both tables are empty, it should just return 0.

I tried the following but didn't get the result expected.

select ifnull(ifnull(max(t1.transid), max(t2.transid)), 0)  
from tbltrans_temp t1 
left join tbltrans as t2

Upvotes: 1

Views: 100

Answers (3)

sgeddes
sgeddes

Reputation: 62851

You were close -- just remove the join:

select ifnull(ifnull(max(t1.transid), max(t2.transid)),0)  
from tbltrans_temp t1, tbltrans t2

http://sqlfiddle.com/#!5/2897f/1

Upvotes: 0

user359040
user359040

Reputation:

Try:

select coalesce((select max(transid) from tbltrans_temp),
                (select max(transid) from tbltrans),
                0)

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

This works using COALESCE:

select coalesce(maxtemptrans, maxtrans, 0)
from (select max(transid) maxtemptrans from tbltrans_temp) t, 
    (select max(transid) maxtrans from tbltrans ) t2

Upvotes: 1

Related Questions