ORDER LIMIT

[Anon]

Hi,
I have a question regarding a mysql query which I don't completly understand. The query is:
$requete= "SELECT id,libelle,site_h,date FROM articles WHERE date > ".date("Y-m-d", (time()-3600))." ORDER BY date LIMIT 20";

Will this query return
-the 20 most recent rows from the articles table(with date > time()-3600)?
-20 rows (with date > time()-3600) from the articles table, but not necessarily the most recent ones?

Thanks in advance,
Emilien Arino

[Anon]

This really depends on the default of mysql(I dont know). But in any case, it would be better to explicitly specify the order. So do a:

ORDER BY date DESC LIMIT 20
(should list the most recent first)

ORDER BY date ASC LIMIT 20
(should list the most historical first)

Hope this helps.

[Anon]

Thanks for the hint.
But what I really wanted to know (it wasn't clear because of my English) is:
If there are more than 20 articles with date > time() - 3600, and if order by date and order by id don't return the rows in the same order (what I mean here is that because of my scripts, "younger" articles don't necessarily have bigger id), will my query return the most recent rows or just the first 20 rows that have date > time() -3600?

Thanks,
Emilien ARINO

[Anon]

Wait, I'm still not sure your question. If your select statement is something like this:

SELECT date, somedata FROM sometable WHERE date > time() - 3600 ORDER BY date DESC

Your result set is already limited to anything greater than 1 hour ago. The ORDER BY date DESC would then take this result set(anthing greater than 1 hour ago) and list the most recent one first and in that order.

Let me know if I'm still confused.. 🙂

-Will

[emilienarino]

I think I am not able to ask this in english....

I can ask it this way:
assuming that my table has let's say 50 rows with
date > time() -3600, what will return :
SELECT date, somedata FROM sometable WHERE date > time() - 3600 ORDER BY date DESC LIMIT 20 ???

Thanks,
Emilien ARINO

[Anon]

Suppose you have data that has date > time()-3600. And suppose the time() returns 2001-02-10 0am. Here is a sample of the data with the select statement "SELECT date, somedata FROM sometable WHERE date > time - 3600 ORDER BY date ASC LIMIT 12"

2001-02-10 1am data1
2001-02-10 2am data2
2001-02-10 3am data3
2001-02-10 4am data4
2001-02-10 5am data5
.
.

2001-02-10 11am data11
2001-02-10 12am data12

Since we used ASC here your result set will first get '2001-02-10 1am data1'
and go down the list.

If you use DESC, your result set will first get '2001-02-10 12am data12'

[emilienarino]

Since time() returns a timestamp.
I suppose time() returns 7200.
So, half an hour ago, time() would have returned
7200 - 1800=5400 ===>>> a row created at that time would have the equivalent of 5400 stored in the column date.
When I request rows where date > time() - 3600, mysql returns rows created less than an hour ago.

What I wanted to know (with all the questions I asked) is how mysql understands thiq query :
SELECT date,somedata FROM sometable WHERE date > time()-3600 ORDER BY DATE DESC LIMIT 20

Will mysql first get all the rows where date > time() - 3600, then order them by date and finally take the first 20?
Or, will mysql get the first 20 rows where date > time()-3600 and then order them by date ?
In other words, does mysql "visit" the entire table before returning results (with my query)??????

Sorry for my English,
Thanks,
Emilien ARINO

[kparker]

No problem with your English, but I do have a question about DESC. Your first question had merely ORDER BY DATE but later ones had ORDER BY DATE DESC. Which is it?

As far as an overall answer to your question, any sql dbms will act as if it did things in stages--i.e. as a pipeline. (If it doesn't do so, it's broken!) The WHERE-clause applies first, so that the rest of the query doesn't even see rows that don't meet the WHERE criteria. Next, the ORDER BY applies, so that it sorts only the rows that meet the selection criteria, rather than all the rows in the database. Finally, the LIMIT clause applies, to the selected rows only, and considering them in the order created by the SORT BY clause.

So, if you are saying

SELECT ... articles WHERE date > $cutoff ORDER BY date LIMIT 20

mysql will (in effect) first gather only those records whose date is past the cutoff, sort them in ASCENDING data order, and then give you the first 20 in that sequence, which will be the OLDEST 20 from those matching the cutoff.

If you wanted the most recent 20, then the correct thing is to ORDER DESC:

SELECT ... articles WHERE date > $cutoff ORDER BY date DESC LIMIT 20

The thing you are apparently worried about (i.e. a random or unspecified 20) could indeed happen, but only if you omit the ORDER BY clause:

SELECT ... articles WHERE date > $cutoff LIMIT 20

Finally, it's worth noting that, if there fewer than 20 rows that match the WHERE criteria, you will get fewer than 20 results.

[[deleted]]

Thanks,
That's exactly what I wanted to know!

Your first question had merely ORDER BY DATE but later ones had ORDER BY DATE DESC. Which is it?

It was ORDER BY DATE DESC but I forgot to mention it in my first post.

Thanks again for your help,
Emilien Arino