Jump to content


Photo

SELECTING only the ones that arn't new


  • Please log in to reply
5 replies to this topic

#1 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 12 March 2006 - 07:00 PM

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?


#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 12 March 2006 - 07:23 PM

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

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.

#3 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 12 March 2006 - 07:46 PM

[!--quoteo(post=354260:date=Mar 12 2006, 02:23 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 12 2006, 02:23 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
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

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

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]

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 12 March 2006 - 10:11 PM

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.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 March 2006 - 10:25 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 lead2gold

lead2gold
  • Members
  • PipPipPip
  • Advanced Member
  • 164 posts
  • LocationOttawa, On

Posted 13 March 2006 - 06:07 PM

[!--quoteo(post=354316:date=Mar 12 2006, 05:11 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 12 2006, 05:11 PM) View Post[/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!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users