Newbie about dates 1

[pablopicasso]

I've a table like:

date | data
---------------------------
2002-09-15 | yeahyeahyeah
2002-09-13 | blahblahblah1
2002-09-13 | blahblahblah2
2002-09-14 | tuttuttut
2002-09-11 | doingdoingdoing

I want to get from this table all the entries in the 3 most recent days (this not the last 3 days counting from today, since it may have been several days since the last entry) In the example, the result would look like:

date | data
---------------------------
2002-09-15 | yeahyeahyeah
2002-09-13 | blahblahblah1
2002-09-13 | blahblahblah2
2002-09-14 | tuttuttut


I've tried this example from the manual, but I can't get what I want.

mysql> SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 3;

I've also tried to use DISTINCT in conjunction with LIMIT 3, but that only gets one row per day

 

[sleipnir214]

I'm pretty sure that you can't get what you want from MySQL using a single query. I recommend that you break the retrieval into two queries from your scripting language.

The first query should read:

SELECT DISTINCT date_column FROM tbl_name ORDER BY date_data DESC LIMIT 3

For your sample data, this query will return three dates: 2002-09-15, 2002-09-14, and 2002-09-13. Use those three dates to construct a second query using MySQL's IN() function like this:

SELECT date_column, data_column FROM tbl_name WHERE date_column IN ('2002-09-15', '2002-09-14', '2002-09-13')

This query will return the four rows of data you want. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[pablopicasso]

Thanx sleipnir, I think that's what I need (now I'll have to figure out how to script between the two querys

BTW, what's the DESC for? .

 

[danielhozac]

DESCending ;-) //Daniel

 

[KarveR]

SELECT date_column, data_column FROM tbl_name WHERE date_column > date_sub(curdate(), interval 3 day) ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.