CODEWITHSUNDEEP
phpmysqlsqlsortingselect
Schlaus
Schlaus

Reputation: 19212

MYSQL: Join two sorted result sets

I'm building a site, where users can create posts for free. They can also pay for some extra visibility for their post, meaning that the paid posts will be shown first in search results. Both paid and unpaid posts reside in the same table, and whether a post is paid or not is calculated from the column paid_until, which simply contains a timestamp.

Now, to make pagination easy, I'd like to be able to get a nice result set from MySQL without additional sorting by PHP, preferably with one query. The total limit for the result set is 20 (as in 20 posts per page), and it should first contain all paid posts (up to 20), and then add 20-n unpaid posts. Both subsets should be sorted by the column modified within themselves. I need to be able to skip the same results later on by setting an OFFSET.

I've tried different variations of JOIN, UNION, and GROUP BY, but have not come up with a solution that would produce the results that I want.

Is there some way to write this:

(SELECT * FROM tbl_post WHERE paid_until >= :time_stamp ORDER BY modified DESC)
UNION
(SELECT * FROM tbl_post WHERE paid_until < :time_stamp ORDER BY modified DESC)

so that it would actually work, since UNION messes up the sorting?

Upvotes: 1

Views: 995

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562651

You can sort the result of a UNION query:

(SELECT * FROM tbl_post WHERE paid_until >= :time_stamp)
UNION
(SELECT * FROM tbl_post WHERE paid_until < :time_stamp)
ORDER BY paid_until >= :time_stamp DESC, modified DESC

It's important to keep the parentheses around the last subquery, so the ORDER BY is clearly outside of the subquery. Otherwise SQL applies the ORDER BY to the subquery alone, and then un-sorts it again in the process of doing UNION.

There's no need to sort in the subqueries, just do it in the final step.

But this query can be simplified further:

SELECT * FROM tbl_post
ORDER BY paid_until >= :time_stamp DESC, modified DESC
LIMIT :pagesize OFFSET :page_times_pagesize

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

Something like:

SELECT col1, col2, col3
FROM (    SELECT *,1 AS SortCol FROM tbl_post WHERE paid_until >= :time_stamp
          UNION
          SELECT *,2 AS SortCol FROM tbl_post WHERE paid_until < :time_stamp
      )AS sub
ORDER BY SortCol, modified DESC

Upvotes: 1

Related Questions