changing sorting criteria after the first result

Question

I am selecting from a database of news articles, and I'd prefer to do it all in one query if possible. In the results, I need a sorting criteria that applies ONLY to the first result.

In my case, the first result must have an image, but the others should be sorted without caring about their image status.

Is this something I can do with some sort of conditionals or user variables in a MySQL query?

Answer 1

I don't think it's possible, as it's effectively 2 queries (the first query the table has to get sorted for, and the second unordered), so you might as well use 2 queries with a LIMIT 1 in the first.

Answer 2

Even if you manage to find a query that looks like one query, it is going to be logicaly two queries. Have a look at MySQL UNION if you really must make it one query (but it will still be 2 logical queries). You can union the image in the first with a limit of 1 and the rest in the second.

Answer 3

Something like this ensures an article with an image on the top.

SELECT
  id,
  title,
  newsdate,
  article 
FROM
  news
ORDER BY
  CASE WHEN HasImage = 'Y' THEN 0 ELSE 1 END,
  newsdate DESC

Unless you define "the first result" closer, of course. This query prefers articles with images, articles without will appear at the end.

Another variant (thanks to le dorfier, who deleted his answer for some reason) would be this:

SELECT
  id,
  title,
  newsdate,
  article 
FROM
  news
ORDER BY
  CASE WHEN id = (
    SELECT MIN(id) FROM news WHERE HasImage = 'Y'
  ) THEN 0 ELSE 1 END,
  newsdate DESC

This sorts the earliest (assuming MIN(id) means "earliest") article with an image to the top.