CODEWITHSUNDEEP
mysqlsql
Valeriu Mazare
Valeriu Mazare

Reputation: 323

MySQL sub-query issue

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:

  1. SELECT most recent entries. It means ORDER BY date(mysql datetime) DESC, id DESC.

  2. 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

Upvotes: 0

Views: 87

Answers (3)

spencer7593
spencer7593

Reputation: 108390

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.)

Upvotes: 3

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 3

akr
akr

Reputation: 739

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.

Upvotes: 0

Related Questions