By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE inputTable (name varchar(20), updated_at DATETIME, children_updated_at DATETIME);
INSERT INTO inputTable (name , updated_at , children_updated_at )
VALUES
( 'User 1', '2024-05-07 10:00:00.000', '2024-05-07 13:00:00.000' ),
( 'User 2', '2024-05-07 11:00:00.000', '2024-05-07 09:00:00.000' ),
( 'User 3', '2024-05-07 12:00:00.000', '2024-05-07 10:00:00.000' );
SELECT * FROM inputTable;
Records: 3 Duplicates: 0 Warnings: 0
| name | updated_at | children_updated_at |
|---|---|---|
| User 1 | 2024-05-07 10:00:00 | 2024-05-07 13:00:00 |
| User 2 | 2024-05-07 11:00:00 | 2024-05-07 09:00:00 |
| User 3 | 2024-05-07 12:00:00 | 2024-05-07 10:00:00 |
select *
from inputTable
order by GREATEST(COALESCE(updated_at, 0), COALESCE(children_updated_at, 0)) desc
| name | updated_at | children_updated_at |
|---|---|---|
| User 1 | 2024-05-07 10:00:00 | 2024-05-07 13:00:00 |
| User 3 | 2024-05-07 12:00:00 | 2024-05-07 10:00:00 |
| User 2 | 2024-05-07 11:00:00 | 2024-05-07 09:00:00 |
With T as(
select *,
GREATEST(updated_at, children_updated_at) as order_by
from inputTable
)
select name , updated_at , children_updated_at
from T
order by order_by desc
| name | updated_at | children_updated_at |
|---|---|---|
| User 1 | 2024-05-07 10:00:00 | 2024-05-07 13:00:00 |
| User 3 | 2024-05-07 12:00:00 | 2024-05-07 10:00:00 |
| User 2 | 2024-05-07 11:00:00 | 2024-05-07 09:00:00 |
select name , updated_at , children_updated_at
from (
select *,
GREATEST(updated_at, children_updated_at) as order_by
from inputTable
) T
order by order_by desc
| name | updated_at | children_updated_at |
|---|---|---|
| User 1 | 2024-05-07 10:00:00 | 2024-05-07 13:00:00 |
| User 3 | 2024-05-07 12:00:00 | 2024-05-07 10:00:00 |
| User 2 | 2024-05-07 11:00:00 | 2024-05-07 09:00:00 |