pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about SELECT and ORDER BY
3+ messages / 3 participants
[nested] [flat]
* Question about SELECT and ORDER BY
@ 2000-02-03 14:34 Vladimir Terziev <vlady@school.digsys.bg>
2000-02-03 15:21 ` Re: [SQL] Question about SELECT and ORDER BY Mark Volpe <volpe.mark@epamail.epa.gov>
2000-02-03 17:46 ` Re: [SQL] Question about SELECT and ORDER BY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Vladimir Terziev @ 2000-02-03 14:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have two tables:
table1 (attr1 text, attr2 int4)
table2 (attr1 text, attr3 int4, attr4 bool)
I have a query:
SELECT table1.attr1 AS ALIAS, attr2 from table1 UNION ALL
SELECT table2.attr1, attr3 from table2 ORDER BY ALIAS;
I that syntax all is OK.
I want to have a result from ORDER BY length(ALIAS), but this is imposible.
When ORDER BY clause is in format ORDER BY length(attr1), the result is not
ORDERED BY length of attr1.
What can I do? Are anybody have an idea?
Vladimir
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Question about SELECT and ORDER BY
2000-02-03 14:34 Question about SELECT and ORDER BY Vladimir Terziev <vlady@school.digsys.bg>
@ 2000-02-03 15:21 ` Mark Volpe <volpe.mark@epamail.epa.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Mark Volpe @ 2000-02-03 15:21 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
I think "ORDER BY" must refer to an existing column.
To get the desired result, try adding a third column containing
the result of the calculation you want to sort by:
SELECT table1.attr1, attr2, length(table.attr1) FROM table1 UNION ALL
SELECT table2.attr1, attr3, length(table2.attr1) FROM table2 ORDER BY
length;
but you have to put up w/ the extra column.
Then again, I also find this to work:
SELECT table1.attr1 AS alias, attr2 INTO table3 FROM table1 UNION ALL
SELECT table2.attr1, attr3 FROM table2;
SELECT * from table3 ORDER BY length(alias);
so go figure :-)
Mark
Vladimir Terziev wrote:
>
> Hi,
>
> I have two tables:
>
> table1 (attr1 text, attr2 int4)
>
> table2 (attr1 text, attr3 int4, attr4 bool)
>
> I have a query:
>
> SELECT table1.attr1 AS ALIAS, attr2 from table1 UNION ALL
> SELECT table2.attr1, attr3 from table2 ORDER BY ALIAS;
>
> I that syntax all is OK.
> I want to have a result from ORDER BY length(ALIAS), but this is imposible.
>
> When ORDER BY clause is in format ORDER BY length(attr1), the result is not
> ORDERED BY length of attr1.
>
> What can I do? Are anybody have an idea?
>
> Vladimir
>
> ************
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Question about SELECT and ORDER BY
2000-02-03 14:34 Question about SELECT and ORDER BY Vladimir Terziev <vlady@school.digsys.bg>
@ 2000-02-03 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2000-02-03 17:46 UTC (permalink / raw)
To: Vladimir Terziev <vlady@school.digsys.bg>; +Cc: pgsql-sql
"Vladimir Terziev" <vlady@school.digsys.bg> writes:
> I have a query:
> SELECT table1.attr1 AS ALIAS, attr2 from table1 UNION ALL
> SELECT table2.attr1, attr3 from table2 ORDER BY ALIAS;
> I want to have a result from ORDER BY length(ALIAS), but this is imposible.
SELECT ... UNION ... ORDER BY is pretty broken in current sources;
in particular adding a hidden column to sort on doesn't work when
there's a UNION. I've looked a little bit at fixing this, and it
looks like it will take the long-threatened querytree restructuring
to deal with it in any sensible fashion. Maybe that'll happen for 7.1.
In the meantime use Mark Volpe's workaround of only sorting on
columns that are in the select list.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-02-03 17:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-03 14:34 Question about SELECT and ORDER BY Vladimir Terziev <vlady@school.digsys.bg>
2000-02-03 15:21 ` Mark Volpe <volpe.mark@epamail.epa.gov>
2000-02-03 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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