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