Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id B802B475C30
	for <pgsql-sql@postgresql.org>; Tue, 14 May 2002 08:06:43 -0400 (EDT)
Received: from temp.joelburton.com (unknown [209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id 1D658475AE9
	for <pgsql-sql@postgresql.org>; Tue, 14 May 2002 08:05:06 -0400 (EDT)
Received: from joeltf0d13cyyp (unknown [209.190.238.100])
	by temp.joelburton.com (Postfix) with SMTP
	id 75B882B981; Tue, 14 May 2002 08:10:12 -0400 (EDT)
From: "Joel Burton" <joel@joelburton.com>
To: "Mathieu Arnold" <mat@mat.cc>, <pgsql-sql@postgresql.org>
Subject: Re: order by <something wierd>
Date: Tue, 14 May 2002 08:05:08 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNKEBACOAA.joel@joelburton.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
Importance: Normal
In-Reply-To: <1713675304.1021384181@andromede.reaumur.absolight.net>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200205/132
X-Sequence-Number: 7768

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Mathieu Arnold
> Sent: Tuesday, May 14, 2002 7:50 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] order by <something wierd>
>
>
> Hi
>
> I have :
>
> table a (int, varchar, int)
>
> 1 | one   | 1
> 2 | two   | 3
> 3 | three | 2
> 4 | four  | 3
> 5 | five  | 2
>
> And I would like to select it and sort it so that the 3rd field
> is first 2,
> then 1 and then 3. so that the result should be :
>
> 1 | one   | 1
> 3 | three | 2
> 5 | five  | 2
> 2 | two   | 3
> 4 | four  | 3
>
> How could I do that ?

Mathieu --

You're asking to have it be sorted as {2,1,3}, but the sample result data
you give seems to sort by {1,2,3}. Or am I misunderstanding your question?

In any event, some possible solutions:

1) custom function

  CREATE OR REPLACE FUNCTION weird_sort(int) RETURNS int AS '
  BEGIN
    IF $1=2 THEN
      RETURN 1;
    ELSIF $1=1 THEN
      RETURN 2;
    ELSE RETURN 3;
    END IF;
  END'
  LANGUAGE plpgsql;

 then

  SELECT * FROM a ORDER BY weird_sort(col3);

 The nice thing about this is that you could index the result of
weird_sort(col3), so it could perform better.


2) inlined in sql

  SELECT * FROM a
  ORDER BY CASE WHEN col3=2 THEN 1
                WHEN col3=1 THEN 2
                ELSE 3
            END;


3) union

  SELECT * FROM a
  WHERE col3=2

  UNION ALL

  SELECT * FROM a
  WHERE col3=1

  UNION ALL

  SELECT * FROM a
  WHERE col3=3;


#1 (with index) or #3 might perform better. Test with your data and see.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant