Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Jackie Jen
Jackie Jen
2,723 Points
Posted by Jackie Jen
Jackie Jen
2,723 Points

What wrong with the result of sql

Table t1

id     status           date                                                                                                                                                                   
1      accept          2014-07-01
2      progress       2014-07-24
3      progress       2014-06-18
4      accept           2014-06-26
5      progress        2014-07-31
6      progress        2014-08-22

SELECT id, status, max(date) FROM t1 GROUP BY status the ans is

id      status      max('date')
1       accept      2014-07-01
2       progress   2014-08-22

I have no idea what going wrong? the ans for status progress should have an id of number '6' instead of 2. can you please advice?

2 Answers

dpjvanvalderen
dpjvanvalderen
6,081 Points
dpjvanvalderen
dpjvanvalderen
6,081 Points

What if you try to select the id of 6 and then try to echo the result. Do you get the results ok then? I know this is not the solution but just wanted to know for further helping.

Jackie Jen
Jackie Jen
2,723 Points
Jackie Jen
Jackie Jen
2,723 Points

Hi Valderen,

the echo result is correct SELECT id, status, max(date) FROM test WHERE id='6'

Matthew Eaton
Matthew Eaton
10,669 Points
Matthew Eaton
Matthew Eaton
10,669 Points

Hi Kenny!

What is the exact MySQL query you used?

I'd imagine to get the results you're after it should look something like this:

SELECT id, status, max(date) FROM [table name] WHERE status = 'progress'

Or substitute 'progress' for whichever status you need it to look up.

(and of course replace [table name] with the actual name of your table.)

Jackie Jen
Jackie Jen
2,723 Points
Jackie Jen
Jackie Jen
2,723 Points

Hi Matthew,

I have add in another column product_id since the id is use as primary key. The Result match now using below mysql query

SELECT id, product_id, status, date FROM [table_name] WHERE date= (SELECT MAX(date)FROM [table_name] WHERE status='progress')

id      product_id   status      max('date')
1       5543            accept      2014-07-01
2       6222           progress   2014-07-24
3       8532           progress       2014-06-18
4       1368           accept           2014-06-26
5       8421           progress        2014-07-31
6       8321           progress        2014-08-22

id    product_id     status          date
2     8321             progress     2014-08-22

Thanks for giving me idea on using status='progress'..^^