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

Question

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.

forpas · Accepted Answer · 2022-09-11T07:57:52.410

1

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.

edited Sep 11 '22 at 07:57

answered Sep 11 '22 at 07:52

forpas

160,666
10
38
76

Thanks alot for your detailed answer. Your both solutions worked for me. Demo is also amazing. – Lily Sep 11 '22 at 10:10

score 0 · Answer 2 · answered Sep 11 '22 at 07:46

0

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

answered Sep 11 '22 at 07:46

flyingfox

13,414
3
24
39

You have put the NOT NULL check & it didn't worked when there is no data available in the table. Please check the accepted answer. – Lily Sep 11 '22 at 10:14

score 0 · Answer 3 · answered Sep 11 '22 at 08:48

0

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;

answered Sep 11 '22 at 08:48

Abhishek Kumar

328
1
4
13

It didn't worked. Because you have put the NOT NULL check & it didn't worked when there is no data available in the table. Please check the accepted answer. – Lily Sep 11 '22 at 10:15

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

3 Answers3