From: | "Joel Burton" <joel(at)joelburton(dot)com> |
---|---|
To: | "Mathieu Arnold" <mat(at)mat(dot)cc>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: order by <something wierd> |
Date: | 2002-05-14 12:05:08 |
Message-ID: | JGEPJNMCKODMDHGOBKDNKEBACOAA.joel@joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Mathieu Arnold
> Sent: Tuesday, May 14, 2002 7:50 AM
> To: pgsql-sql(at)postgresql(dot)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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 2002-05-14 12:07:05 | Re: order by <something wierd> |
Previous Message | Mathieu Arnold | 2002-05-14 12:04:10 | Re: order by <something wierd> |