pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: order by <something wierd>
8+ messages / 6 participants
[nested] [flat]
* Re: order by <something wierd>
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: Mathieu Arnold <mat@mat.cc>
4 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: mat@mat.cc; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* order by <something wierd>
@ 2002-05-14 11:49 Mathieu Arnold <mat@mat.cc>
0 siblings, 5 replies; 8+ messages in thread
From: Mathieu Arnold @ 2002-05-14 11:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-14 12:01 Marin Dimitrov <marin.dimitrov@sirma.bg>
parent: Mathieu Arnold <mat@mat.cc>
4 siblings, 1 reply; 8+ messages in thread
From: Marin Dimitrov @ 2002-05-14 12:01 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; pgsql-sql
----- 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. "
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-14 12:04 Mathieu Arnold <mat@mat.cc>
parent: Mathieu Arnold <mat@mat.cc>
4 siblings, 0 replies; 8+ messages in thread
From: Mathieu Arnold @ 2002-05-14 12:04 UTC (permalink / raw)
To: pgsql-sql
--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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-14 12:05 Joel Burton <joel@joelburton.com>
parent: Mathieu Arnold <mat@mat.cc>
4 siblings, 1 reply; 8+ messages in thread
From: Joel Burton @ 2002-05-14 12:05 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-14 12:07 D'Arcy J.M. Cain <darcy@druid.net>
parent: Mathieu Arnold <mat@mat.cc>
4 siblings, 0 replies; 8+ messages in thread
From: D'Arcy J.M. Cain @ 2002-05-14 12:07 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-14 12:28 Mathieu Arnold <mat@mat.cc>
parent: Joel Burton <joel@joelburton.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mathieu Arnold @ 2002-05-14 12:28 UTC (permalink / raw)
To: Joel Burton <joel@joelburton.com>; pgsql-sql
--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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: order by <something wierd>
@ 2002-05-15 02:06 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Marin Dimitrov <marin.dimitrov@sirma.bg>
0 siblings, 0 replies; 8+ messages in thread
From: Christopher Kings-Lynne @ 2002-05-15 02:06 UTC (permalink / raw)
To: Marin Dimitrov <marin.dimitrov@sirma.bg>; Mathieu Arnold <mat@mat.cc>; pgsql-sql
> ----- 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-05-15 02:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-14 11:49 order by <something wierd> Mathieu Arnold <mat@mat.cc>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2002-05-14 12:01 ` Marin Dimitrov <marin.dimitrov@sirma.bg>
2002-05-15 02:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-05-14 12:04 ` Mathieu Arnold <mat@mat.cc>
2002-05-14 12:05 ` Joel Burton <joel@joelburton.com>
2002-05-14 12:28 ` Mathieu Arnold <mat@mat.cc>
2002-05-14 12:07 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox