## The Question :

*264 people think this question is useful*

I have a query that looks like this:

`SELECT article FROM table1 ORDER BY publish_date LIMIT 20`

How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by the `publish_date`

field?

If it’s the last one, you’re not guaranteed to really get the most recent 20 articles.

*The Question Comments :*

## The Answer 1

*259 people think this answer is useful*

It will order first, then get the first 20. A database will also process anything in the `WHERE`

clause before `ORDER BY`

.

## The Answer 2

*40 people think this answer is useful*

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

`SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15`

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

`SELECT * FROM tbl LIMIT 95,18446744073709551615;`

With one argument, the value specifies the number of rows to return from the beginning of the result set:

`SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows`

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

All details on: http://dev.mysql.com/doc/refman/5.0/en/select.html

## The Answer 3

*12 people think this answer is useful*

Just as @James says, it will order all records, then get the first 20 rows.

As it is so, you are guaranteed to get the 20 first published articles, the newer ones will not be shown.

In your situation, I recommend that you add `desc`

to `order by publish_date`

, if you want the newest articles, then the newest article will be first.

If you need to keep the result in ascending order, and still only want the 10 newest articles you can ask mysql to sort your result two times.

This query below will sort the result descending and limit the result to 10 (that is the query inside the parenthesis). It will still be sorted in descending order, and we are not satisfied with that, so we ask mysql to sort it one more time. Now we have the newest result on the last row.

select t.article
from
(select article, publish_date
from table1
order by publish_date desc limit 10) t
order by t.publish_date asc;

If you need all columns, it is done this way:

select t.*
from
(select *
from table1
order by publish_date desc limit 10) t
order by t.publish_date asc;

I use this technique when I manually write queries to examine the database for various things. I have not used it in a production environment, but now when I bench marked it, the extra sorting does not impact the performance.

## The Answer 4

*7 people think this answer is useful*

If there is a suitable index, in this case on the `publish_date`

field, then MySQL need not scan the whole index to get the 20 records requested – the 20 records will be found at the start of the index. But if there is no suitable index, then a full scan of the table will be needed.

There is a *MySQL Performance Blog article* from 2009 on this.

## The Answer 5

*7 people think this answer is useful*

You could add [asc] or [desc] at the end of the order by to get the earliest or latest records

For example, this will give you the latest records first

ORDER BY stamp DESC

Append the `LIMIT`

clause after `ORDER BY`

## The Answer 6

*7 people think this answer is useful*

You can use this code
`SELECT article FROM table1 ORDER BY publish_date LIMIT 0,10`

where 0 is a start limit of record & 10 number of record

## The Answer 7

*2 people think this answer is useful*

LIMIT is usually applied as the last operation, so the result will first be sorted and then limited to 20. In fact, sorting will stop as soon as first 20 sorted results are found.

## The Answer 8

*0 people think this answer is useful*

Could be simplified to this:

`SELECT article FROM table1 ORDER BY publish_date DESC FETCH FIRST 20 ROWS ONLY;`

You could also add many argument in the `ORDER BY`

that is just comma separated like: `ORDER BY publish_date, tab2, tab3 DESC`

etc…