CODEWITHSUNDEEP
phpmysqldatabase
ORStudios
ORStudios

Reputation: 3233

MYSQL Combine Two Date Fields And Then Order BY

I have 2 tables in MYSQL that will be joined by a foreign key. Each table has its own date field. What I would like to do is combine these two date fields together and then order by the combined information. I understand that you can order by multiple fields but I don't think that combines them.

Example

Join by tickets.ticket_id = ticket_history.ticket_id

Table 1

ticket_date

Table 2

ticket_history_date

Goal combine the two date fields and then order the rows by the combined dates

Upvotes: 1

Views: 3961

Answers (2)

SureshKumar Vegesna
SureshKumar Vegesna

Reputation: 1202

The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.

Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:

select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;

Upvotes: 1

liquorvicar
liquorvicar

Reputation: 6126

Let's assume that you want the latest of the two dates, you can do something like this

SELECT
    [some info],
    GREATEST( ticket_date, ticket_history_date ) as latest_date
FROM
    [tables and join]
ORDER BY
    latest_date

If you wanted the oldest date you could use LEAST rather than GREATEST. If one of the dates might be NULL then you might want to look into COALESCE

Upvotes: 6

Related Questions