I made this query a view, called "MEMBERP", no problem:
SELECT MemberID, ereStart, DateModified, MembershipExpires, MemberSince,
Category, Boardster, GROUP_CONCAT(DISTINCT Salutation ORDER BY Rank) AS
Salutation, GROUP_CONCAT(DISTINCT GivenName ORDER BY Rank) AS GivenName,
GROUP_CONCAT(DISTINCT
;>>> 2011/10/19 17:00 +0200, Dotan Cohen
mysql> select * from beers b outer join colours c on (b.colour = c.ID);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'outer join colours c on (b
On Thu, Oct 20, 2011 at 00:11, Derek Downey wrote:
> Ah-hah! :)
>
> Actually, I did something similar to that a month or so ago. I ran into a
> speed limitation on a not-small database (~3mill rows). So be careful.
>
> Luckily in my case, I put all the 'minimum' ids in a memory table with an
> i
On Thu, Oct 20, 2011 at 00:06, Basil Daoust wrote:
> For me given the sample data the following worked.
> The inner select says find all first messages, the outer says give me all
> messages that are thus not first messages.
>
> select * from table1 where messageID NOT IN (
> select messageID from
On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
wrote:
> What you are describing is a FULL OUTER JOIN. This is not supported, yet, in
> MySQL. Â We only support INNER, NATURAL, LEFT, and RIGHT.
>
> To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a
> RIGHT like this:
>
I also want to say Thank you Shawn for your valuable contribution. On the
note on Resumes -I thought I would put it out here. My company is looking
for an excellent MySQL DBA (Oracle and MSSQL experience a plus) Please send
me your resume.
Thanks!
Sabika
On Wed, Oct 19, 2011 at 12:30 PM, Shawn Gr
Can you rotate that table daily, thus keeping it small?
You can then move yesterdays data somewhere for post processing.
try an insert if it fails generate the new table from the template, done.
Thus no checking other then once a day when the insert fails.
On Wed, Oct 19, 2011 at 2:54 PM, Johan D
Ah-hah! :)
Actually, I did something similar to that a month or so ago. I ran into a speed
limitation on a not-small database (~3mill rows). So be careful.
Luckily in my case, I put all the 'minimum' ids in a memory table with an index
and it solved it. It also was a cleanup script, and not som
For me given the sample data the following worked.
The inner select says find all first messages, the outer says give me all
messages that are thus not first messages.
select * from table1 where messageID NOT IN (
select messageID from table1
group by userID
)
Some times just playing with the dat
- Original Message -
> From: "Wm Mussatto"
>
> I've turned on the reporting of full table scans into the show query log
> and Wordpress has a large number of full table scans so it could easily be
Never trust your vendor to know their way around your systems. Check where you
can add
- Original Message -
> From: "Brian Dunning"
>
> Can someone tell me if what I'm trying to do can be done more
> efficiently? I just got off the phone with Rackspace when my server
> was hung up, and they found a whole bunch of this one same query was
> all stacked up with waiting queries
On Tue, October 18, 2011 22:40, Johan De Meersman wrote:
> - Original Message -
>> From: "Ryan Mark"
>>
>> (WordPress does not like to share an app server) and added memcache.
>
> Really? We run dozens of the thing on a couple of virtuals with no
> problems at all. Then again, we don't exa
On 10/19/2011 13:29, Michael Dykman wrote:
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride t
Can someone tell me if what I'm trying to do can be done more efficiently? I
just got off the phone with Rackspace when my server was hung up, and they
found a whole bunch of this one same query was all stacked up with waiting
queries and locked.
Here's the query:
$query = "insert ignore into
On 10/19/2011 13:19, Dotan Cohen wrote:
...
Thank you Shawn! I see that I am getting support right from the top!
So far as I understand, an outer join should return all matched and
unmatched rows (essentially all rows) from both tables. So it is not
clear to me what is the difference between a
Thanks Shawn!
Ninus from Montreal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
You could do a GROUP_CONCAT to get you close:
SELECT userID, SUBSTRING_INDEX(GROUP_CONCAT(messageID), ',', 1) messageList
FROM table
GROUP BY userID
| userID | messageList |
|--|---|
| 71| 984|
| 73| 441, 489|
| 74|
On Wed, Oct 19, 2011 at 12:29 PM, Michael Dykman wrote:
> While we have him online, I think we could all take a moment and be
> grateful
> for the contributions of Shawn Green.
>
> When I see the Oracle-bashing on this list, I am often reminded that we
> still have a hard-core MySQL developer who
I'm afraid that what you are looking for simply cannot be done with MySQL
alone. You will need to pare your results at the application layer.
Remember that rows have no inherent order except for conforming to any
ORDER BY clause contained within the query.
- md
On Wed, Oct 19, 2011 at 1:27 PM,
While we have him online, I think we could all take a moment and be grateful
for the contributions of Shawn Green.
When I see the Oracle-bashing on this list, I am often reminded that we
still have a hard-core MySQL developer who has survived the ride to Sun and
again to Oracle who is still provid
Assuming a table such this:
| ID | messageID | userID |
||-||
| 1 | 345 | 71 |
| 2 | 984 | 71 |
| 3 | 461 | 72 |
| 4 | 156 | 73 |
| 5 | 441 | 73 |
| 6 | 489 | 73 |
| 7 | 483 | 74 |
|
On Wed, Oct 19, 2011 at 18:00, Shawn Green (MySQL)
wrote:
> This is a simple misunderstanding. From the page you quote, the syntax
> patterns for an OUTER join are these:
>
> Â | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
>
> Â | table_reference NATURAL [{LEFT|RIGHT}
Am 19.10.2011 17:45, schrieb Tim Johnson:
> * Reindl Harald [111018 23:24]:
>> Am 19.10.2011 01:36, schrieb Tim Johnson:
try "mysql -u tim -p"
>>> Same error:
>>> ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
>>> password: NO)
> and enter yur password in the fol
Hello Dotan,
On 10/19/2011 09:57, Dotan Cohen wrote:
mysql> select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set (0.0
* Reindl Harald [111018 23:24]:
> Am 19.10.2011 01:36, schrieb Tim Johnson:
> >> try "mysql -u tim -p"
> > Same error:
> > ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
> > password: NO)
> >> > and enter yur password in the followed dialog
> > Doesn't even ask for the pwd
On Wed, Oct 19, 2011 at 16:33, Michael Dykman wrote:
> Try this. Â I sometime get wierd results when I fail to use aliases in a
> join. Â Also, the parentheses are required.
> Â - md
> Â select * from beers b inner join colours c on (b.colour = c.ID);
>
Thank you Michael. That does work, however
mysql> select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set (0.00 sec)
mysql> select * from colours;
+++
| i
- Original Message -
> From: "Sergei Petrunia"
>
> Create index on (importance, company_id) (or
> (company_id,importance), should
> not matter). When that index is present, the query should use ref
> access on it using both key parts (if by some crazy reason it doesn't, use
> FORCE
> IN
On Wed, Oct 19, 2011 at 05:09:13PM +1100, Wayne W wrote:
> Hi,
>
> I asked this question over on stackoverflow - basically I have a query
> and when using EXPLAIN I see that around 2400 rows are still being
> scanned. I'd added various indexes but it cannot make it perform any
> better.
>
> I wou
Am 19.10.2011 01:36, schrieb Tim Johnson:
>> try "mysql -u tim -p"
> Same error:
> ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
> password: NO)
>> > and enter yur password in the followed dialog
> Doesn't even ask for the pwd..
then your mysql CLIENT is broken or somehow
30 matches
Mail list logo
