CODEWITHSUNDEEP
mysqlsqlsortingjoinsql-order-by
Karl
Karl

Reputation: 37

Sorting relational tables using only 1 field in mysql

I have 2 relational tables and I joined them, and I want it to be sorted by the same field.

Tables:

posts fields: id, post, created

reposts fields: id,post_id, created

Here's what I currently have:

SELECT 
p.post as post,
p.created as post_created,
rp.created as repost_created
FROM posts p
LEFT JOIN reposts rp
ON rp.post_id = p.id
ORDER BY rp.created DESC, p.created DESC

And here's the result of that code:

{Code Result1

But what I want is to sort the whole data, base on the created field as a whole, regarding if it has repost_created or if it's null, it will base on the post_created field. Like this:

[Desired Result2

Thank you very much!

Upvotes: 0

Views: 43

Answers (2)

ali azizan
ali azizan

Reputation: 311

i think your purpose is

SELECT 
p.post as post,
p.created as post_created,
rp.created as repost_created
FROM posts p
LEFT JOIN reposts rp
ON rp.post_id = p.id
ORDER BY  isnull( p.created ,rp.created)

Upvotes: 0

Nick
Nick

Reputation: 147146

It looks like you just need to COALESCE the value of rp.created with p.created and sort on that; that way if rp.created is not NULL you'll sort on that date, otherwise you'll sort on p.created:

ORDER BY COALESCE(rp.created, p.created) DESC

Note that you may want an additional criterion to break ties (when two rows have the same datetime value), you could perhaps use

ORDER BY COALESCE(rp.created, p.created) DESC, rp.id IS NULL

this will sort reposts ahead of posts at the same time.

Upvotes: 2

Related Questions