instead of
> SELECT name, (CASE WHEN bar = 34 THEN 0 ELSE 1 END) AS rank> FROM foo> WHERE
> bar = 34> OR baz > 100> ORDER BY rank ASC, baz DESC> LIMIT 5;
you can use order the query evaluation to perform inner subquery first
select distinct foo.name,foo.bar,baz.bar from foo where exists (select * from
foo baz where baz.baz>100 LIMIT 5)
where foo.bar=34order by foo.rank LIMIT 5
which then feeds correlated records to outer query
(which then yields results based on sort-merge of inner then outer queries)
HTHMartin Gainty ______________________________________________ Disclaimer and
confidentiality note Everything in this e-mail and any attachments relates to
the official business of Sender. This transmission is of a confidential nature
and Sender does not endorse distribution to any party other than intended
recipient. Sender does not necessarily endorse content contained within this
transmission. > From: [EMAIL PROTECTED]> To: mysql@lists.mysql.com> Subject:
Result ordering> Date: Sun, 30 Nov 2008 23:33:47 +0100> > > Hi, I'm retrieving
the name of some records where either one of two > criteria are satisfied:> >
SELECT name> FROM foo> WHERE bar = 34> OR baz > 100> ORDER BY baz DESC> LIMIT
5;> > I would like to sort that result set, such that the records matching >
bar = 34 occur before records with baz > 100. I could do this using a > CASE:>
> SELECT name, (CASE WHEN bar = 34 THEN 0 ELSE 1 END) AS rank> FROM foo> WHERE
bar = 34> OR baz > 100> ORDER BY rank ASC, baz DESC> LIMIT 5;> > But I need the
name DISTINCT, how can I accomplish this? I could wrap > the above select in a
SELECT DISTINCT name FROM (SELECT ... ) but is > there a nicer way to use the
"rank" than including it in the result set?> > Thanks!> > Morten> > > -- >
MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To
unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]>
_________________________________________________________________
Access your email online and on the go with Windows Live Hotmail.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_access_112008
