Re: How do I do this query efficiently?

2006/11/16, Peter Brawley [EMAIL PROTECTED]: Michael, SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; I had almost the same problem and I found this solution very smart... even smarter than I can understand

Re: How do I do this query efficiently?

Michael, SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; I had almost the same problem and I found this solution very smart... even smarter than I can understand :) Can someone explain to me why/how this query works

How do I do this query efficiently?

Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I

Re: How do I do this query efficiently?

for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT

Re: How do I do this query efficiently?

- From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? for every id I want exactly one row, namely the row with the maximum value

Re: How do I do this query efficiently?

Hi, Thanks for your quick answer, but unfortunately this query does not return the value column of the row; and that is the column I am ultimately interested in (in combination with the id). Regards, Sebastiaan Peter Brawley wrote: for every id I want exactly one row, namely the row with

Re: How do I do this query efficiently?

Try this !!! - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: peter brawley [EMAIL PROTECTED] Cc: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:28:46 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently

Re: How do I do this query efficiently?

; - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? for every id I want exactly one row, namely the row

Re: How do I do this query efficiently?

Right, if you want the value column you need too, you need a different query ... SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; PB Sebastiaan van Erk wrote: Hi, Thanks for your quick answer, but unfortunately

Re: How do I do this query efficiently?

Pretty slick. - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 13, 2006 10:43:26 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? Right, if you want the value column

Re: How do I do this query efficiently?

Wow, neat. I didn't think you could do that without a subquery somewhere. Learned a cool new trick today. Thanks! Regards, Sebastiaan Peter Brawley wrote: Right, if you want the value column you need too, you need a different query ... SELECT t1.id, t1.version, t1.value FROM data t1 LEFT