Issue order by date

RediXe

I have two tables:
T1
t1_id, location, description

T2
t2_id, t1_id, date

the id fields are tinyint auto increment pk. location is type VARCHAR, description is type TEXT, and date is type DATE.

I am trying to get each location, description and the date ordered by the date in desc order. I only need the most recent date per location. (Need to know when the location was last used but I want to keep a log of all uses just in case.)

I've seemed to have gotten it to work somewhat with the query:

$q = "select t1.location, t1.description, t2.date from t1, t2 where t2.t1_id = t1.t1_id order by DATE_FORMAT(t2.date, '%m/%d/%y') desc";

I'm not 100% how's it giving me data. I just show 2012/01/19, 2011/08/10, 2012/01/20 when it should be 2011, 2012/01/19, 2012/01/20. I'm thinking because the 2012/01/19 has multiple entries in t2 it might be causing some issue.

bradgrafelman

You'll probably want to GROUP BY the columns location, date, and description (just to make the strict SQL gurus happy) in that order.

RediXe

This is the updated query:

$q = "select t1.location, t1.description, t2.date from t1, t2 where t2.t1_id = t1.t1_id group by t1.location, t2.date, t1.description order by DATE_FORMAT(t2.date, '%m/%d/%y') desc";

I've tried a few other things but nothing is giving me what I want. From what I can tell I need to use a subquery.

Google lead me to: http://stackoverflow.com/questions/1368331/show-only-most-recent-date-from-joined-mysql-table

bradgrafelman

Get rid of the DATE_FORMAT() in the ORDER BY - it doesn't make any sense to format a date in that way (for a couple of reasons) before ordering by it.

Otherwise, you're going to say things like May 1st, 2012 ("05/01/12") comes after January 1st, 2013 ("01/01/13") since "05" is greater than "01". This is why the YYYY-MM-DD format was used for DATE columns - natural ordering of dates uses that pattern.

RediXe

This is what I currently have:

$q = "select t1.*, t2.* from t1, t2 where t2.t1_id = t1.t1_id group by t1.location, t2.date, t1.description order by EXTRACT(Year FROM t2.date), EXTRACT(Month FROM t2.date), EXTRACT(Day FROM t2.date)";

This seems to be sorting by the earliest date for each location.

bradgrafelman

You simply need to order by the date column - nothing else.

RediXe

bradgrafelman;10995078 wrote:
You simply need to order by the date column - nothing else.

I wish it was that simple.

$q = "select t1.*, t2.* from t1, t2 where t2.t1_id = t1.t1_id group by t1.location, t2.date, t1.description order by t2.date";

Result is showing dates in the following order:
t1_id date
2011-10-04
2012-01-20
2012-01-20
2012-01-19
2012-01-19
2012-01-20
2012-01-20

The 2012-01-20 above the 2012-01-19 have older dates from 2011 which makes me believe that it's using those dates to order by. These results are what I show on my site (using adodb).

bradgrafelman

Any chance you can post some sample data from T1 and T2?

RediXe

Here is a sql dump of the db (it's small)

http://pastie.org/3232203

Weedpacket

If you only want the most recent date for each flush_id:

Select flush_id, max(date) from lastflush group by flush_id

It should be possible to combine that with the other table (whether joining it or as a subquery).