From: Peter Brawley Date: April 25 2005 8:43pm Subject: Re: Query question List-Archive: http://lists.mysql.com/mysql/183106 Message-Id: <426D5671.9070206@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426D56716881=======" --=======AVGMAIL-426D56716881======= Content-Type: multipart/alternative; boundary=------------050507060502040405010103 --------------050507060502040405010103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: > Peter, > > I'm unfamiliar with the "@d := " section you describe. Is this psudo > code or real syntax? > > thanks, > > Jeff > > > > -----Original Message----- > *From:* Peter Brawley [mailto:peter.brawley@stripped] > *Sent:* Monday, April 25, 2005 4:17 PM > *To:* Jeff McKeon > *Cc:* mysql@stripped > *Subject:* Re: Query question > > 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 >>> >>> >>> >>> >> >> >> >> >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 > > --------------050507060502040405010103 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using?

PB

Jeff McKeon wrote:
Message
Peter,
 
I'm unfamiliar with the "@d := " section you describe.  Is this psudo code or real syntax?
 
thanks,
 
Jeff
 
 
-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question

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


    


  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--------------050507060502040405010103-- --=======AVGMAIL-426D56716881======= 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-426D56716881=======--