MySQL sub-query issue

Question

SELECT * 
FROM (SELECT *
  FROM content
  WHERE topic='$id' AND active='1'
  ORDER BY date DESC, id DESC
  LIMIT 4) a
ORDER BY a.likes DESC

I have this query. I want it to select 4 entries from content table, and sort entries as follows:

SELECT most recent entries. It means ORDER BY date(mysql datetime) DESC, id DESC.
From those 4 selected, order them by likes(mysql INT) DESC

When runing my query, it returns wrong results. It selects entries matching this criteria WHERE topic='$id' AND active='1' . It sorts entries by likes DESC, but it ignore this criteria ORDER BY date DESC, id DESC, so it shows me results with a smaller id first.

What can be the reason? Thank you in advance

Your internal select should not contain an order by cause it will be ignored once you order by with your global query — ooo
– ooo, Commented Apr 1, 2014 at 18:03
@Philibobby: this isn't true; MySQL will honor the ORDER BY inside the inline view; that ORDER BY will be applied prior to the LIMIT clause. — spencer7593
– spencer7593, Commented Apr 1, 2014 at 18:06
@spencer7593 is correct. A subquery with ORDER BY and LIMIT will never ignore the ORDER BY. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 1, 2014 at 18:07
No, it will not ignore. It will use the internal ORDER BY for selecting the 4 rows and then use the external ORDER BY for ordering/displaying the (4) rows. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 1, 2014 at 18:09
Oh ok I get it, there is a LIMIT. Sorry about that! @ypercube is right. — ooo
– ooo, Commented Apr 1, 2014 at 18:10

Hamlet Hakobyan · Accepted Answer · 2014-04-01 18:14:04Z

3

After OP edits, the correct query will be

SELECT * 
FROM (SELECT *
  FROM content
  WHERE topic='$id' AND active='1'
  ORDER BY date DESC, id DESC
  LIMIT 4) a
ORDER BY a.likes DESC, date DESC, id DESC

edited Apr 1, 2014 at 18:14

answered Apr 1, 2014 at 18:04

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Hamlet Hakobyan Over a year ago

@OlafDietsche, can you explain what he want?

spencer7593 Over a year ago

I don't think either of these satisfy the specifications OP provided.

Hamlet Hakobyan Over a year ago

@spencer7593, see comment above.

Valeriu Mazare Over a year ago

Just edited my question. What i just tried is

SELECT *  FROM (SELECT * FROM content WHERE topic='$id' AND active='1' ORDER BY date DESC, id DESC LIMIT 4)a ORDER BY a.likes DESC

But it doesnt work as well. The idea of what i want is 'select 4 most recent entries', then sort them by likes DESC

Valeriu Mazare Over a year ago

@HamletHakobyan, it doesnt work. First, i think the query ignores the desired priority. I want first to select 4 most recent entries. It means "select 4 entries and order them by date DESC, if dates are the same , order by id DESC" -> when having those 4, order them by likes DESC.

|

spencer7593 · Accepted Answer · 2014-04-01 18:38:02Z

The ORDER BY on the outermost query specifies the order of the rows returned. No other order of rows is guaranteed or implied.

From the original question (prior to the edit) sounds like OP wanted the rows returned in descending order by the integer value of the likes column. That is, OP wanted to specify:

ORDER BY a.likes DESC

on the outermost query.

The rows returned by the query will be returned in the sequence defined by ORDER BY on the outermost query. No other sequencing of rows is guaranteed.

If OP wants the rows returned in a specific order, then the list of expressions in the ORDER BY clause on the outermost query will need to be specified differently. For example:

ORDER BY a.likes DESC, a.date DESC, a.id DESC

--or--

ORDER BY a.date DESC, a.likes DESC, a.id DESC

The ORDER BY in the inline view will be honored by the inline view query; but once that inline view query is materialized, and is referenced as a row source by the outer query, that ORDER BY is gone. The outer query is free to access and return the rows from the inline view (derived table) in any order it wants; the outer query isn't required to honor the ORDER BY on the inline view query; the outer query just sees the derived table as a row set, like any other table.

(This is assuming that "likes" is a column in the content table, and not a result derived from some other table. We don't see what columns your query is returning, because you are using * as the SELECT list.)

(If that isn't what OP is looking for, OP can elaborate on the requirements for the specified resultset. Everything else looks right in OP query, for getting the four "latest" rows within the inline view.)

akr · Accepted Answer · 2014-04-01 18:06:47Z

0

Try this:
SELECT *
  FROM content
  WHERE topic='$id' AND active='1'
  ORDER BY date DESC, likes desc
  LIMIT 4

You dont need another level of select to do order by.

answered Apr 1, 2014 at 18:06

akr

7394 silver badges15 bronze badges

2 Comments

spencer7593 Over a year ago

@user2411275: it's true you don't need an inline view to perform an ORDER BY; but OP specifies that those four "latest" rows are to be returned in descending order by "likes" (which we assume is a column in the content table.)

akr Over a year ago

thanks spencer. Valeriu Mazare - You cannot work on the same resultset and want to order them differently. order by on the likes column would definitely affect the order of the values in the date column

Collectives™ on Stack Overflow

MySQL sub-query issue

3 Answers 3

10 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related