Order by views limiting, then order by another column

Question

I have a query that selects * from my database ordering by views and limiting by 4:

SELECT * FROM articles WHERE visible = 1 ORDER BY views LIMIT 4;

But in the same query I want to find all other rows ordering by column updated_at.

I haved tryied things like this, but doesn't works:

(SELECT * FROM articles ORDER BY views DESC LIMIT 4)
UNION
(SELECT * FROM articles ORDER BY updated_at DESC);

The propose this are "pinning" the 4 hotest articles on home page and then ordering by time was updated.

Have any way to ORDER BY multiple ways in the same query without repeat the rows?

How can I do this?

Answer 1

From MySQL documentation:

... The default behavior for UNION is that duplicate rows are removed from the result. ...

And

... If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway. ...

So the trick here is to use limit in the second query (it is up to you to choose the limit):

(SELECT * FROM articles WHERE visible = 1 ORDER BY views DESC LIMIT 4)
UNION
(SELECT * FROM articles WHERE visible = 1 ORDER BY updated_at DESC LIMIT 100);

The query was tested in MySQL 5.6 and 5.7.