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 unde
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 quer
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 JOIN
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 wa
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
rsion;
- 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 on
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
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 wit
ssage -
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 t
>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
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
> This will give me sort of what I am looking for, but it shows the
> UserKey in the first column and I want the UserID. I have tried a few
> others but just got errors.
> SELECT f.UserKey, UserID FriendID, Name FriendName
> FROM User u, FriendList f
> WHERE f.FriendKey = u.UserKey
> ORDER BY f.U
I have the following 2 tables.
CREATE TABLE User (
UserKey INT NOT NULL AUTO_INCREMENT,
UserIDCHAR(16) NOT NULL UNIQUE ,
Name VARCHAR(20),
PRIMARY KEY (UserKey)
);
CREATE TABLE FriendList(
UserKey INT NOT NULL,
FriendKey INT NOT NULL,
PRIMARY KEY (UserKey, FriendKey)
I have the following 2 tables.
CREATE TABLE User (
UserKey INT NOT NULL AUTO_INCREMENT,
UserIDCHAR(16) NOT NULL UNIQUE ,
Name VARCHAR(20),
PRIMARY KEY (UserKey)
);
CREATE TABLE FriendList(
UserKey INT NOT NULL,
FriendKey INT NOT NULL,
PRIMARY KEY (UserKey, FriendKey)
upper/lower, or all upper,
etc.
I'd double check all your case to ensure everything is identical.
-Original Message-
From: John Boshier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 31, 2003 12:36 PM
To: [EMAIL PROTECTED]
Subject: How Do I Do This In Version 3.23.56?
I have
I have spent all day working on a PHP/MySQL system offline using MySQL
version 4.0.15-nt. One particular SQL statement that works perfectly
wouldn't work when I uploaded it to my web site. It seems that my ISP
is using version 3.23.56, and I have written a SQL statement that will
only work on 4.04
display another sent of 7
>records but it just keeps displaying the same.
>
>+---+
>| version() |
>+---+
>| 3.23.36 |
>+---+
>
>How do I do this.
>
>Adrian
>
-
Before posting, please c
Jeff S Wheeler wrote:
> [This is an email copy of a Usenet post to "mailing.database.mysql"]
>
> Just insert a row with the initial value you want minus one, and then
> delete it. That will make the table do what you want.
>
> mysql> INSERT INTO Orders VALUES (-1, "", NOW(), -1, 125478);
>
[This is an email copy of a Usenet post to "mailing.database.mysql"]
Just insert a row with the initial value you want minus one, and then
delete it. That will make the table do what you want.
mysql> INSERT INTO Orders VALUES (-1, "", NOW(), -1, 125478);
Query OK, 1 row affected (0.03 sec)
ravel t, hotel h
WHERE t.nome_hotel = h.hotelname;
you have to create (or specify) indexes seperately.
[...]
> > What I want to do is replace the nome_hotel field with the ids of the
> > hotel table for names that match the hotel.hotelname in the
> > travel.nome_hotel. How
| Angela |
> | Marmari Beach |
> | Summer Palace |
> | Aegean Village |
> +-------------+
>
> What I want to do is replace the nome_hotel field with the ids of the
> hotel table for names that match the hotel.hotelname in the
> travel.nome_hotel. How do I do t
in the
travel.nome_hotel. How do I do this.
TIA
Adrian
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL
22 matches
Mail list logo
