Martin AJ Martin AJ - 1 year ago 57
SQL Question

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

I read somewhere:

You must use
clause when you're using
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 IN ($ids)
GROUP BY type, post_id, other_id
ORDER BY (seen IS NULL) desc, MAX(date_time) desc, MAX( desc

is a PHP variable contains a string like this:
1, 2, 4, 76

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download