Postgres Pro
Downloads
Home   >   mailing lists

Thread: Controlling order of evaluation?

Controlling order of evaluation?

From
Jerry LeVan
Date:
I have an srf sql function "annual_report(<year>)" that
as 14 columns, a category, 12 month numeric columns,  and a total
numeric column.

The function finds monthly totals for each category (which is the
target of "order by")
and the grand total for the specified year.

I have another sql function annual_profit_loss(<year>) that summarizes
the total
for each month and the grand total for each year. ( There is a text
column that
serves a label.) It generates a summary for the annual_report function
in essence.

I have hoped that

select * from annual_report(2003)
union
select * from annual_profit_loss(2003)

would print the last select last ;( but it inserts the last selection
alphabetically into the rows of the annual_report depending on the label
field... I suppose I could use a label "zzGrand Totals", but that just
does not look right.

Is there any way I can force the last select to appear last?

Jerry

Re: Controlling order of evaluation?

From
Russell Smith
Date:
On Wed, 29 Sep 2004 08:16 am, Jerry LeVan wrote:
> I have an srf sql function "annual_report(<year>)" that
> as 14 columns, a category, 12 month numeric columns,  and a total
> numeric column.
>
> The function finds monthly totals for each category (which is the
> target of "order by")
> and the grand total for the specified year.
>
> I have another sql function annual_profit_loss(<year>) that summarizes
> the total
> for each month and the grand total for each year. ( There is a text
> column that
> serves a label.) It generates a summary for the annual_report function
> in essence.
>
> I have hoped that
>
> select * from annual_report(2003)
> union
> select * from annual_profit_loss(2003)
Order by title_column = 'Grand Total', month

that will put all rows not containing grand total at the top, sorted by month.
Then grand total's sorted by month.  something like that.

>

> would print the last select last ;( but it inserts the last selection
> alphabetically into the rows of the annual_report depending on the label
> field... I suppose I could use a label "zzGrand Totals", but that just
> does not look right.
>
> Is there any way I can force the last select to appear last?
>
> Jerry
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

Re: Controlling order of evaluation?

From
Andre Maasikas
Date:
Jerry LeVan wrote:

> I have hoped that
>
> select * from annual_report(2003)
> union
> select * from annual_profit_loss(2003)
>
> would print the last select last ;( but it inserts the last selection
> alphabetically into the rows of the annual_report depending on the label
> field... I suppose I could use a label "zzGrand Totals", but that just
> does not look right.

I guess that it's the UNION operator that needs to elimiate duplicate rows
by definition and therefore does sorting to do this. From your
description it seems that you could use UNION ALL instead which might
work ok for you.

> Is there any way I can force the last select to appear last?
>
AFAIK the only sure way is to use ORDER BY if you have
some suitable column or can make one up. Usually using expressions
one can do pretty complicated stuff but it seems that in case of
UNION only select list items are allowed in ORDER BY. But then again you
can do it as subselect and then ....

select a from (
  select a,b from ...
  union all
  select a,b from ...
) u
order by case when b='Last' then 'zzzz' else b end;

Andre