CODEWITHSUNDEEP
phpmysqlsql
Martin AJ
Martin AJ

Reputation: 6697

Should I use LIMIT when I'm using ORDER BY?

I read somewhere:

You must use LIMIT clause when you're using ORDER BY clause. If you don't, you sometimes will be surprisingly faced with different results.

Is that true?

Actually here is my query: 

SELECT SUM(score) score, type, context, post_id, other_id, MAX(date_time)
FROM `events` e
WHERE e.id IN ($ids)
GROUP BY type, post_id, other_id
ORDER BY (seen IS NULL) desc, MAX(date_time) desc, MAX(e.id) desc

Note: $ids is a PHP variable contains a string like this: 1, 2, 4, 76.

Should I use LIMIT in my query? If yes/not, then why?

Upvotes: 0

Views: 51

Answers (1)

Mike Robinson
Mike Robinson

Reputation: 8945

No, you don't have to use LIMIT if you don't want to. (I have no idea what the author meant by "surprisingly faced with different results ...")

The ORDER BY clause, as you very well know, specifies that the result-set should be sorted. Likewise, LIMIT, as you also very well know, specifies that you only want to see the first n rows of that result-set (sorted or not).

The two clauses, then, are entirely independent of one another. If you want all of the (sorted, or not) rows that would be returned by the query, you are under no obligation to use LIMIT, and, in fact, ought not do so.

Upvotes: 1

Related Questions