Jump to content

SELECTING only the ones that arn't new


lead2gold

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?
Link to comment
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.
Link to comment
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]
Link to comment
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.