Thread: order by
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 Arnold
Try select * from a order by 3,1,2; Regards, Christoph > > 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 Arnold > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
----- Original Message ----- From: "Mathieu Arnold" > 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 ? > gate09=# select * from a order by 3,1,2; c1 | c2 | c3 ----+-------+---- 1 | one | 1 3 | three | 2 5 | five | 2 2 | two | 3 4 | four | 3 hth, Marin ---- "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. "
--On mardi 14 mai 2002 13:49 +0200 Mathieu Arnold <mat@mat.cc> wrote: > 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 : next time, I'll reread what I say, the order I want is : 3 | three | 2 5 | five | 2 1 | one | 1 2 | two | 3 4 | four | 3 -- Mathieu Arnold
> -----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
RETURN2; 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
On May 14, 2002 07:49 am, Mathieu Arnold wrote:
> 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 ?
If I understand what you are asking:
SELECT c3, c2, c1 FROM a ORDER BY c3, c1;
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
I have a table with a column called "weight" that is a text field, but in this case, contains float values, such as ".8" or "0.25". Might also be null. How can I sort them in numeric order instead of textual order? Thanks.
--On mardi 14 mai 2002 08:05 -0400 Joel Burton <joel@joelburton.com> wrote:
> 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?
you're right, I messed up my example :)
I'll buy the second solution, it's the easier to put on.
> 2) inlined in sql
>
> SELECT * FROM a
> ORDER BY CASE WHEN col3=2 THEN 1
> WHEN col3=1 THEN 2
> ELSE 3
> END;
--
Mathieu Arnold
> -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Frank Morton > Sent: Tuesday, May 14, 2002 8:24 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] sort text field numerically > > > I have a table with a column called "weight" that > is a text field, but in this case, contains float values, > such as ".8" or "0.25". Might also be null. > > How can I sort them in numeric order instead of > textual order? Thanks. select * from floats_table order by text_float_field::float; will work, as long as there are no non-float items in the table (if there are, you could use an ORDER BY CASE ... to look for non-numeric characters and sort these differently, and sort the rest by converting to floats as ::float) HTH. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
> ----- Original Message ----- > From: "Mathieu Arnold" > > > 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 ? > > > > gate09=# select * from a order by 3,1,2; This won't work - it will order by the third param, then first, then second... Chris