SELECTING only the ones that arn't new

[lead2gold]

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?

[wickning1]

[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.

[lead2gold]

[!--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]

[wickning1]

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.

[fenway]

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.

[lead2gold]

[!--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!