Jump to content

Archived

This topic is now archived and is closed to further replies.

lead2gold

SELECTING only the ones that arn't new

Recommended Posts

I have a table that has a date_started column. This table can hold many items that all share the same person_id column.

Is there a way i can select into the database and select all but the newest per person?

ie: lets say this information existed in "table_name" with these 2 columns: person_id,date_started

1,2002-10-23 10:00:00
1,2003-10-23 10:00:00
1,2004-10-23 10:00:00
1,2005-10-23 10:00:00
2,2004-10-23 10:00:00
2,2005-10-23 10:00:00
3,2005-10-23 10:00:00



I want the output to be:
1,2002-10-23 10:00:00
1,2003-10-23 10:00:00
1,2004-10-23 10:00:00
2,2004-10-23 10:00:00


Basically if there is older data associated with the user, then i want to retrieve it... if there is only 1 thing then well... there is nothing to retrieve from that person... because there is nothing older then the newest thing.

Does that make sense?

Share this post


Link to post
Share on other sites
[code]SELECT t.person_id, t.date_started FROM table_name t
INNER JOIN (
   SELECT person_id, MAX(date_started) as max_date FROM table_name GROUP BY person_id
) f WHERE t.person_id=f.person_id AND t.date_started != f.max_date[/code]

Note: If there are two entries with the same person and same date (down to the second), and the date is the most recent, then this will skip both entries.

Share this post


Link to post
Share on other sites
[!--quoteo(post=354260:date=Mar 12 2006, 02:23 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 12 2006, 02:23 PM) [snapback]354260[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]SELECT t.person_id, t.date_started FROM table_name t
INNER JOIN (
   SELECT person_id, MAX(date_started) as max_date FROM table_name GROUP BY person_id
) f WHERE t.person_id=f.person_id AND t.date_started != f.max_date[/code]

Note: If there are two entries with the same person and same date (down to the second), and the date is the most recent, then this will skip both entries.
[/quote]


You've been an awsome help!!
I had to reword the code slightly to work (naturally as i didn't give you enough information of my tables) but i get the following error now... any ideas?

SQL STRING
[code]SELECT a.* FROM appraisals a ".
        "INNER JOIN ( SELECT uid, MAX(date_started) as max_date FROM appraisals GROUP BY uid ) f".
        "  WHERE a.uid=f.uid AND a.date_started != f.max_date[/code]

ERROR MSG
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]You have an error in your SQL syntax near '( SELECT uid, MAX(date_started) as max_date FROM appraisals GROUP BY uid ) f WH' at line 1[/quote]

Share this post


Link to post
Share on other sites
Sounds like your MySQL version doesn't support subqueries. What version are you running? Consider upgrading to 4.1 or 5.0. Until then you will probably have to do this on the PHP end. I can't think of a pre-4.1 solution.

Share this post


Link to post
Share on other sites
Temporary tables in place of the subquery are always an option, albeit an annoying one, if you're intent on doing it in MySQL alone.

Share this post


Link to post
Share on other sites
[!--quoteo(post=354316:date=Mar 12 2006, 05:11 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 12 2006, 05:11 PM) [snapback]354316[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sounds like your MySQL version doesn't support subqueries. What version are you running? Consider upgrading to 4.1 or 5.0. Until then you will probably have to do this on the PHP end. I can't think of a pre-4.1 solution.
[/quote]

Thats ok, i'll have the server thats hosting me to upgrade. I belive your right, the version isn't at 4.1.

Thanks for your help and thanks to the other guy for your feedback as well.
For now i'll just do 2 selects. I'm a bit of a noob, i wasn't even aware of the MAX() variable and that it would help me out too, so thats a plus.

Thanks again!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.