Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

3

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search