Thread: Question about SELECT and ORDER BY

Question about SELECT and ORDER BY

From

"Vladimir Terziev"

Date:

03 February 2000, 12:35:32

   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

Re: [SQL] Question about SELECT and ORDER BY

From

Mark Volpe

Date:

03 February 2000, 13:23:33

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
> 
> ************

Re: [SQL] Question about SELECT and ORDER BY

From

Tom Lane

Date:

03 February 2000, 15:48:35

"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