Order by whichever date is earliest

Question

Here's the deal. I've got articles and issues, both of which can have a release date. (Issues must, articles can.) Whichever date is earlier is the release date.

I've got all the logic for that basically worked out, and it all works perfectly, except I can't quite figure out the order clause to order by this release date. What is the cleanest SQL method to implement this functionality? I know basic SQL structure and whatnot, but not this sort of advanced logic...

Thanks in advance! --Matchu

Answer 1

This will work:

order by 
case when article.date is not null and article.date<issue.date then article.date
else issue.date end

Answer 2

You order by the base date (the magazine) and then the issue date.

order by issue, article.

Also if you have an issue number then you can just use the numbers to order your stuff since the volume and issue numvers tends to consistently increase over time.

Answer 3

Assuming you've got a table with two dates (this part is not clear to me) and ArticleReleaseDate is nullable, you'll want something like

ORDER BY (CASE 
   WHEN IssueReleaseDate < ArticleReleaseDate OR ArticleReleaseDate IS NULL
   THEN IssueReleaseDate
   ELSE ArticleReleaseDate
   END)

Answer 4

Let's assume you've got several Articles which each belong to an Issue. (as in, Issues of a magazine, I'm guessing ). You want to sort them by when they came out, which is either the Issue release date, or in some cases the Articles have their own release date. Whichever date is earlier is the sort value.

To do this in one line we have to make some assumptions about your database. I'll show you MySQL, but perhaps someone will provide a translation to another RDBMS if needed.

Article.all :include => :issue, :order => "if( (isnull( articles.release_date ) OR articles.release_date > issues.release_date), issues.release_date, articles.release_date )"

Answer 5

Rather than locating the sort logic in the ORDER BY clause (as others propose here), I think you should expose the sort order to the 'end user' via the SELECT clause; if your DBMS is SQL-92 compliant, you can use just correlation names in the ORDER BY clause e.g.

SELECT COALESCE(CASE 
                   WHEN V1.issue_release_date <= V1.article_release_date 
                      THEN V1.issue_release_date
                   ELSE V1.article_release_date 
                END, V1.issue_release_date) AS considered_release_date, 
       <other columns here>
  FROM ...
 ORDER
    BY considered_release_date, <other correlation names here>;