List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:December 1 2008 12:25am
Subject:RE: Result ordering
View as plain text  
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:
my.lists@stripped> To: mysql@stripped> 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/mysql?unsub=1 
_________________________________________________________________
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
Thread
Result orderingMorten30 Nov
  • Re: Result orderingAndy Shellam30 Nov
    • Re: Result orderingMorten30 Nov
  • RE: Result orderingMartin Gainty1 Dec