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

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.

CodePudding user response：

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.

CodePudding user response：

Is the below sql meet your demand?

select IF(timestamp IS NOT NULL,timestamp,'1900-01-01 00:00:00.000') as `timestamp` 
       from employees order by timestamp desc LIMIT 1

CodePudding user response：

For these types of queries, you can use CASE statements in MY-SQL

SELECT 
CASE
    WHEN e.timestamp IS NOT NULL 
        THEN e.timestamp
    ELSE '1900-01-01 00:00:00.000'
END AS 'timestamp_col'
FROM employees AS e
ORDER BY 1 DESC
LIMIT 1;