From: Peter Brawley Date: April 25 2005 8:17pm Subject: Re: Query question List-Archive: http://lists.mysql.com/mysql/183100 Message-Id: <426D5042.8080208@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426D50422691=======" --=======AVGMAIL-426D50422691======= Content-Type: multipart/alternative; boundary=------------030509090008020805060109 --------------030509090008020805060109 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND datestamp=@d; PB ----- Jeff McKeon wrote: >Thanks all but I don't have a mysql version high enough for subqueries. > >Thanks, > >Jeff > > > >>-----Original Message----- >>From: Peter Brawley [mailto:peter.brawley@stripped] >>Sent: Monday, April 25, 2005 4:01 PM >>To: Jeff McKeon >>Cc: mysql@stripped >>Subject: Re: Query question >> >> >>Jeff, >> >>Something like ... >> >>SELECT * >>FROM table2 AS a >>WHERE datestamp = ( >> SELECT MAX( b.datestamp ) >> FROM table2 AS b >> WHERE a.parentID = b.parentID >>); >> >>PB >> >>----- >> >> >>Jeff McKeon wrote: >> >> >> >>>I have a table that contains records that link back to a >>> >>> >>main talbe in >> >> >>>a many to one configuration linked by table1.id = table2.parentid >>> >>>Table1 (one) >>>Table2 (many) >>> >>>I want to pull the latest records from table2 for each >>> >>> >>record in table1 >> >> >>>where certain criteria applie. >>> >>>So, if record 100 in table1 links to 5 corresponding records >>> >>> >>in table2, >> >> >>>I want to pull the latest record from table2 where table2.parentid = >>>100 and table2.user not like 'john' >>> >>>There is a datestamp field in table2. >>> >>>I just can't figure out how to do this. >>> >>>Thanks, >>> >>>Jeff >>> >>> >>> >>> >>> >>> >>-- >>No virus found in this outgoing message. >>Checked by AVG Anti-Virus. >>Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 >> >> >> >> > > > > --------------030509090008020805060109 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jeff,

Then do it with 2 queries,

SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT * FROM table2 WHERE parentID = X AND datestamp=@d;PB

-----

Jeff McKeon wrote:

Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-----


Jeff McKeon wrote:

I have a table that contains records that link back to a

main talbe in

a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each

record in table1

where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records

in table2,

I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

--------------030509090008020805060109-- --=======AVGMAIL-426D50422691======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 --=======AVGMAIL-426D50422691=======--