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! While you're at it, check out some resources Treehouse students have shared here.

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

How to Retrieve the latest date based on each of the status in sql

Table t1

id     status           date                                                                                                                                                                   
1      accept          2014-07-05
2      accept          2014-07-01
3      progress      2014-09-10
4      progress      2014-10-01

How can i retrieve the latest date of each status. the answer will be only id=1 status=accept date=2014-07-05 & id=4 status=progress date=2014-10-01.

below is my code to retrieve but only able to retrieve based on the latest date but how to based on status and date? "SELECT * FROM t1 WHERE date = (SELECT MAX(date) FROM t1)" ;

3 Answers

Gergő Bogdán
Gergő Bogdán
6,664 Points
Gergő Bogdán
Gergő Bogdán
6,664 Points

Try this:

SELECT status, max(date)
FROM t1
GROUP BY status
Jackie Jen
Jackie Jen
2,723 Points
Jackie Jen
Jackie Jen
2,723 Points

Thanks Gergo, I have been searching google for whole afternoon. thanks it really help

Gergő Bogdán
Gergő Bogdán
6,664 Points
Gergő Bogdán
Gergő Bogdán
6,664 Points

I am lad, that I could help.

Here are some webpages where you can start learning sql or look for answers in case you are blocked: 1. W3C SQL Tutorial 2. SQL Course 3. SQL Zoo

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

Hi Gergo, 

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?

Gergő Bogdán
Gergő Bogdán
6,664 Points
Gergő Bogdán
Gergő Bogdán
6,664 Points

Is the id field marked as primary key on the table ?

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

Hi Gergo,

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 helping all the time..^^