Select different fields based on data

Databases
1

Let’s say I have an orders table with the following fields and data:


--------------------------------------------------
| id | date_captured       | date_invoiced       |
--------------------------------------------------
| 1  | 2010-09-10 12:20:00 | 0000-00-00 00:00:00 |
--------------------------------------------------
| 2  | 0000-00-00 00:00:00 | 2010-09-12 21:45:00 |
--------------------------------------------------
| 3  | 2010-09-13 05:19:00 | 0000-00-00 00:00:00 |
--------------------------------------------------

What I want to do is have a query that will pull the following results:


----------------------------
| id | order_date          |
----------------------------
| 1  | 2010-09-10 12:20:00 |
----------------------------
| 2  | 2010-09-12 21:45:00 |
----------------------------
| 3  | 2010-09-13 05:19:00 |
----------------------------

I want to pull two fields, the ID and a date, but the date that gets pulled is one of the two dates in the table - date_captured or date_invoiced. In the orders table only one of those two fields would be set to something other than ‘0000-00-00 00:00:00’ - How can I format a query to pull this?

2

Excellent, I have no idea why I did not think of that. Thanks for the help.

One more question… if both date_captured and date_invoiced are set I want to select date_captured, regardless of which one is larger (the MAX). Is that possible?

3

i think you want GREATER, not MAX, and without the GROUP BY

:slight_smile:

4 

SELECT 
    id
  , MAX(date_captured, date_invoiced) AS order_date
FROM orders
GROUP BY id
5

Oops, yes :blush:

6

Instead of 0000-00-00 00:00:00 use NULL and then you can use COALESCE with your columns, it chooses the first NON-NULL value.

SELECT COALESCE(date_captured, date_invoiced) FROM yourtable