List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:September 22 2001 8:21am
Subject:Re: MySQL-4.0 UNION still broken :D
View as plain text  
Hi!

>>>>> "Fournier" == Fournier Jocelyn <Fournier> writes:

Fournier> Oups, I wanted to say :
Fournier> SELECT pseudo FROM t1 UNION ORDER BY pseudo LIMIT 1 SELECT pseudo1 FROM t1;

Fournier> ;)

The problem is that this syntax will not work if you would like to
have a separate limit for the last SELECT statement.

I have played with the idea of being able to say:

(SELECT pseudo FROM t1 UNION ORDER BY pseudo LIMIT 10)
UNION
(SELECT pseudo1 FROM t1 ORDER BY pseudo1 LIMIT 10)
ORDER BY ... LIMIT ...

The problem is that this would be confusing with the normal usage of UNION
where the last ORDER BY apply to all rows.

Fournier> ----- Original Message -----
Fournier> From: "Fournier Jocelyn [Presence-PC]" <joc@stripped>
Fournier> To: <monty@stripped>
Fournier> Sent: Saturday, September 22, 2001 4:34 AM
Fournier> Subject: Re: MySQL-4.0 UNION still broken :D


>> Hi,
>> 
>> Why not something like :
>> 
>> SELECT pseudo FROM t1 UNION LIMIT 1 ORDER BY pseudo SELECT pseudo1 FROM
Fournier> t1;
>> if you want to apply the LIMIT & ORDER BY to the whole UNION result, and
Fournier> the
>> standard syntax if you want to apply LIMIT & ORDER BY to each member of
Fournier> the
>> UNION.

<cut>

>> > Fournier> I have more questions about union ;)
>> >
>> > Fournier> How to make an ORDER BY or a LIMIT only on the second part of
>> the UNION, and
>> > Fournier> not on all the union result ?
>> > Fournier> Ex :
>> >
>> > mysql> SELECT pseudo,pseudo1 FROM t1;
>> > Fournier> +--------+----------+
>> > Fournier> | pseudo | pseudo1  |
>> > Fournier> +--------+----------+
>> > Fournier> | joce   | testtt   |
>> > Fournier> | joce   | tsestset |
>> > Fournier> | dekad  | joce     |
>> > Fournier> +--------+----------+
>> > Fournier> 3 rows in set (0.00 sec)
>> >
>> > mysql> SELECT pseudo FROM t1 UNION SELECT pseudo1 FROM t1 LIMIT 1;
>> > Fournier> +--------+
>> > Fournier> | pseudo |
>> > Fournier> +--------+
>> > Fournier> | dekad  |
>> > Fournier> +--------+
>> > Fournier> 1 row in set (0.00 sec)
>> >
>> > We didn't apply this, as we didn't know how to get this nicely into
>> > the union syntax.
>> >
>> > Any suggestions?
>> > (Do you know how any other databases that uses 'limit' solves this ?)
>> >
>> > mysql> SELECT pseudo FROM t1 UNION SELECT pseudo1 FROM t1 ORDER BY
Fournier> pseudo1
>> > Fournier> LIMIT 1;
>> > Fournier> ERROR 1054: Unknown column 'pseudo1' in 'order clause'
>> >
>> > Regards,
>> > Monty
Thread
Re: MySQL-4.0 UNION still broken :DMichael Widenius22 Sep
  • newbieSri22 Sep
    • newbieSri23 Sep
      • Re: newbieSasha Pachev23 Sep
        • Re: newbieSri23 Sep
          • RE: newbieVenu23 Sep
      • Re: newbieSergei Golubchik23 Sep
        • Re: newbieSri24 Sep
          • Re: WITH CUBESergei Golubchik24 Sep
bk commit into 4.0 tree (lenz:1.1662)lenz16 Dec