Skip to content
Advertisement

If rows exist in table then return value from table ELSE return a default value. – MySQL

I have to write a query which will return the latest timestamp column’s value from a table if rows exist.

If there is no row(s) in table then it return the default value.

I have write the following query in MS-SQL which is working fine for me.

IF Exists (select * from employees)
      Begin
           select  TOP 1  timestamp from employees order by timestamp desc;
      End
Else
      Begin
            select '1900-01-01 00:00:00.000' as timestamp;
      End

But , now I need to write this query in MySQL.

I have tried a-lot of methods but no luck.

In MySQL:

IF (select EXISTS (select * from employees)) 
    BEGIN
           select  timestamp from employees order by timestamp desc LIMIT 1
      END
ELSE
      BEGIN
            select '1900-01-01 00:00:00.000' as timestamp
      END

Can anyone please comment how can I do this.

Advertisement

Answer

Use UNION ALL:

(SELECT timestamp FROM employees ORDER BY timestamp DESC LIMIT 1)
UNION ALL
SELECT '1900-01-01 00:00:00.000' 
FROM DUAL -- you may remove FROM DUAL if MySql's version is 8.0+
WHERE NOT EXISTS (SELECT 1 FROM employees)

or, if timestamp is not nullable:

SELECT COALESCE(MAX(timestamp), '1900-01-01 00:00:00.000') AS timestamp
FROM employees;

See the demo.

Advertisement