CODEWITHSUNDEEP
sqloracle-sqldeveloper
Ofer
Ofer

Reputation: 5219

How to get the last date from 2 dates in select statement

I am trying to create select statement in procedure.

Here is my code:

SELECT u.id, max(a.time), max(b.time)
FROM buy a, sell b, users u
WHERE a.user_id = u.id AND b.user_id = u.id
GROUP BY u.id;

I need to take the last date between max(a.time) and max(b.time).

Thanks in advance

Upvotes: 1

Views: 130

Answers (3)

Jesus
Jesus

Reputation: 31

What about this?

SELECT 
  u.id, 
  CASE 
    WHEN max(a.time) > max(b.time) THEN max(a.time)
    ELSE max(b.time)
  END
FROM buy a, sell b, users u
WHERE a.user_id = u.id AND b.user_id = u.id
GROUP BY u.id;

Upvotes: 3

manurajhada
manurajhada

Reputation: 5390

SELECT U.ID, 
CASE WHEN MAX(A.TIME) > MAX(B.TIME) THEN MAX(A.TIME)
     ELSE MAX(B.TIME) 
END
FROM BUY A, SELL B, USERS U
WHERE A.USER_ID = U.ID AND B.USER_ID = U.ID
GROUP BY U.ID;

Upvotes: 0

Bohemian
Bohemian

Reputation: 425358

SELECT u.id, max(case when a.time > b.time then a.time else b.time end)
FROM buy a, sell b, users u
WHERE a.user_id = u.id AND b.user_id = u.id
GROUP BY u.id;

If your DB supports aliased queries, try this which would be much more efficient:

SELECT u.id, case when a_max > b_max then a_max else b_max end
FROM (SELECT u.id, max(a.time) as a_max, max(b.time) as b_max
    FROM buy a, sell b, users u
    WHERE a.user_id = u.id AND b.user_id = u.id
    GROUP BY u.id) x;

The inner query could use indexes if they existed to get the max for each column quickly.


While we're at it, let's re-code it using the modern join syntax:

SELECT u.id, max(case when a.time > b.time then a.time else b.time end)
FROM buy a
JOIN sell b ON b.user_id = u.id
JOIN users u ON a.user_id = u.id
GROUP BY u.id;

Upvotes: 2

Related Questions