X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3572D5280C for ; Thu, 30 Jun 2005 08:57:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00516-05 for ; Thu, 30 Jun 2005 11:57:14 +0000 (GMT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by svr1.postgresql.org (Postfix) with ESMTP id B4FBF52918 for ; Thu, 30 Jun 2005 08:57:06 -0300 (ADT) Received: from website.brandywine.com (mailcheck.brandywine.com [146.145.113.9]) by svr4.postgresql.org (Postfix) with ESMTP id E93F45AFC22 for ; Thu, 30 Jun 2005 11:57:09 +0000 (GMT) X-eSCM-MailFrom: Received: from BAM_EXCHANGE (10.1.0.100) by website.brandywine.com (10.1.0.9); Thu, 30 June 2005 08:09:23 -0400 Received: from [10.1.0.60] (GEN-WAYNE [10.1.0.60]) by bam_exchange.brandywine.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id NYJBN4A3; Thu, 30 Jun 2005 07:43:35 -0400 Message-ID: <42C3DE6F.9080105@brandywine.com> Date: Thu, 30 Jun 2005 07:58:39 -0400 From: Daryl Richter Reply-To: daryl@brandywine.com Organization: Brandywine Asset Management User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: ORDER records based on parameters in IN clause References: <1120057638.8208.117.camel@state.g2switchworks.com> <42C3A0A3.40101@dltmedia.nl> In-Reply-To: <42C3A0A3.40101@dltmedia.nl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/375 X-Sequence-Number: 21976 M.D.G. Lange wrote: > Another option would be: > > SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450; > > This should give you the results in the right order... > Per the SQL Standard, the rows of a table have no ordering. The result of a SELECT is just a derived table. Assuming a row order is *always* a bug. If you want an explicit row order you *must* use an ORDER BY clause. I would also recommend to you a saying that I learned many years ago, "Filter on the server, sort on the client." > - Michiel > > Scott Marlowe wrote: > >> On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote: >> >> >>> fair enough. but a simple order by id would never work. >>> >>> >> >> >> Try this: >> >> select *, >> case when id=2003 then 1 when id=1342 then 2 >> when id=799 then 3 when id=1450 then 4 end as ob >> from tablename where id in (2003,1342,799,1450) order by ob; >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> >> >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- Daryl Richter Platform Author & Director of Technology v: 610.361.1000 x202 (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com ))