true
true

Reputation: 221

MySQL: How to "order by" a subquery column while also using a parent query "order by"?

  1. I have two tables.
  2. They both have date and item_id columns.
  3. I'm inner joining them on item_id.
  4. The results should be ordered by both date columns together
  5. The code below works, producing the correct result set...
  6. However they are only ordered by the first table's date

Code

SELECT t1.*
FROM   table_one_items t1
       INNER JOIN (SELECT t2.item_id,
                          t2.date_favorited
                   FROM   table_two_favorites t2
                   WHERE  t2.user_id IN( 1, 2, 3 )) t3
               ON t1.item_id = t3.item_id
                   OR t1.user_id IN( 1, 2, 3 )
WHERE  t1.published = 1
GROUP  BY t1.item_id
ORDER  BY t3.date_favorited DESC,
          t1.date_created DESC 

Question:

I'm looking for the items in chronological order despite coming from a table and a subquery table with their own date columns. How do I go about doing this?

What I've tried:

I have tried moving the t3.date around and ordering the subquery itself but that doesn't work. Any attempt to modify the subquery changes the result set, and I can't think of another way to write it to get the same results but with the correct ordering. I'm aware there might be a better way to write this query, but I haven't been able to figure that out either. I feel lucky this works!

Additional Info:

The first table is an index of all items, while the second table is a "favorites" table that only includes items that have been favorited by users. With t1.item_id = t3.item_id OR t1.user_id IN(1,2,3) I'm essentially getting items in t1 that are made by these users OR items that have been favorited by these users.

Examples:

Table One "Item Table": (Date is "date created")

item_id 1 user_id 2 date_created 2015-01-1 00:00:00
item_id 2 user_id 2 date_created 2015-01-3 00:00:00
item_id 3 user_id 1 date_created 2015-01-5 00:00:00
item_id 4 user_id 3 date_created 2015-01-7 00:00:00

Table Two "User Favorites": (Date is "date favorited")

item_id 2 user_id 2 date_favorited 2015-01-6 00:00:00
item_id 1 user_id 2 date_favorited 2015-01-6 00:00:00
item_id 1 user_id 3 date_favorited 2015-01-8 00:00:00

Desired Results:

item_id 3 (ordered by date_created: 2015-01-5 00:00:00)
item_id 4 (ordered by date_created: 2015-01-7 00:00:00)
item_id 2 (ordered by date_favorited: 2015-01-6 00:00:00)
item_id 1 (ordered by date_favorited: 2015-01-8 00:00:00)

Current Results:

item_id 1 (ordered by date_created)
item_id 2 (ordered by date_created)
item_id 3 (ordered by date_created)
item_id 4 (ordered by date_created)

Any help would be greatly appreciated. (And I'll be sure to mark the answer!)

Upvotes: 0

Views: 532

Answers (2)

Ben Adams
Ben Adams

Reputation: 3431

Try using a CASE statement in the ORDER BY to select which date you want, so:

ORDER BY 
    CASE
        WHEN t3.date > t1.date AND t3.item_id = t1.item_id THEN t3.date
        ELSE t1.date
    END DESC

If you want to sort by the maximum of the two dates. However I'm not sure your subquery would be doing what you expect; unless items created by the user are always in table_two regardless of whether they had favorited them

Upvotes: 1

Suresh Gautam
Suresh Gautam

Reputation: 893

Try following with order by clause:

ORDER BY 
    COALESCE(t3.date,t1.date) DESC;

Above query can help ordering for your requirements though you have opportunity to tune your SQL.

Upvotes: 0

Related Questions