From: Martin Gainty Date: December 1 2008 12:25am Subject: RE: Result ordering List-Archive: http://lists.mysql.com/mysql/215408 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_8afa24ca-0e73-41cb-9b09-d2a573890e46_" --_8afa24ca-0e73-41cb-9b09-d2a573890e46_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable instead of=20 > SELECT name=2C (CASE WHEN bar =3D 34 THEN 0 ELSE 1 END) AS rank> FROM foo= > WHERE bar =3D 34> OR baz > 100> ORDER BY rank ASC=2C baz DESC> LIMIT 5=3B you can use order the query evaluation to perform inner subquery first =20 select distinct foo.name=2Cfoo.bar=2Cbaz.bar from foo where exists (select = * from foo baz where baz.baz>100 LIMIT 5)=20 where foo.bar=3D34order by foo.rank LIMIT 5 =20 which then feeds correlated records to outer query=20 (which then yields results based on sort-merge of inner then outer queries) =20 HTHMartin Gainty ______________________________________________ Disclaimer = and confidentiality note Everything in this e-mail and any attachments rela= tes to the official business of Sender. This transmission is of a confident= ial 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= om> Subject: Result ordering> Date: Sun=2C 30 Nov 2008 23:33:47 +0100> > > = Hi=2C I'm retrieving the name of some records where either one of two > cri= teria are satisfied:> > SELECT name> FROM foo> WHERE bar =3D 34> OR baz > 1= 00> ORDER BY baz DESC> LIMIT 5=3B> > I would like to sort that result set= =2C such that the records matching > bar =3D 34 occur before records with b= az > 100. I could do this using a > CASE:> > SELECT name=2C (CASE WHEN bar = =3D 34 THEN 0 ELSE 1 END) AS rank> FROM foo> WHERE bar =3D 34> OR baz > 100= > ORDER BY rank ASC=2C baz DESC> LIMIT 5=3B> > But I need the name DISTINCT= =2C 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 "ran= k" than including it in the result set?> > Thanks!> > Morten> > > -- > MySQ= L General Mailing List> For list archives: http://lists.mysql.com/mysql> To= unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped>=20 _________________________________________________________________ Access your email online and on the go with Windows Live Hotmail. http://windowslive.com/Explore/Hotmail?ocid=3DTXT_TAGLM_WL_hotmail_acq_acce= ss_112008= --_8afa24ca-0e73-41cb-9b09-d2a573890e46_--