August 29th, 2007 by depesz | Tags: , | 8 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?

his query:

select yday.region,yday.yday,mtd.mtd,target.target from
(SELECT dr.region,sum(f.collection) as yday FROM fact_collection f,dim_time dt,dim_region dr
WHERE dt.bus_date=current_date-45 AND dt.time_id=f.time_id AND f.region_id=dr.region_id GROUP BY dr.region) as yday,
(SELECT dr.region,sum(f.collection) as mtd FROM fact_collection f,dim_time dt, dim_region dr
WHERE dt.bus_date BETWEEN date_trunc('month',current_date-45) AND current_date AND dt.time_id=f.time_id
AND f.region_id=dr.region_id GROUP BY dr.region) as mtd,
(SELECT dr.region,sum(f.target)as target FROM dim_region dr,fact_collection_target f ,dim_time dt
WHERE dt.month_id=f.month_id AND dr.region_id=f.region_id AND dt.month_id=5 GROUP BY dr.region) as target
where yday.region=mtd.region and mtd.region=target.region
union
select 'Total' as region,sum(foo.yday),sum(foo.mtd),sum(foo.target)
from
(
select yday.region,yday.yday,mtd.mtd,target.target from
(SELECT dr.region,sum(f.collection) as yday FROM fact_collection f,dim_time dt,dim_region dr
WHERE dt.bus_date=current_date-45 AND dt.time_id=f.time_id AND f.region_id=dr.region_id GROUP BY dr.region) as yday,
(SELECT dr.region,sum(f.collection) as mtd FROM fact_collection f,dim_time dt, dim_region dr
WHERE dt.bus_date BETWEEN date_trunc('month',current_date-45) AND current_date AND dt.time_id=f.time_id
AND f.region_id=dr.region_id GROUP BY dr.region) as mtd,
(SELECT dr.region,sum(f.target)as target FROM dim_region dr,fact_collection_target f ,dim_time dt
WHERE dt.month_id=f.month_id AND dr.region_id=f.region_id AND dt.month_id=5 GROUP BY dr.region) as target
where yday.region=mtd.region and mtd.region=target.region
)as foo;

outputted results as:

"Central";144034;2594109;100394040
"East";144344;2591332;101193660
"North";144910;2591508;100558860
"South";143733;2588980;100303770
"Total";720490;12960302;502879290
"West";143469;2594373;100428960

while he wanted “Total" to be at the end.

so, how to you do it?

there are at least 3 possible choices:

  1. change “union" to “union all" – union has the feature that is removes duplicate rows. to do so, it has to sort source resultsets – thus killing order. union all doesn't sort as it doesn't remove duplicates – so order will be “as expected"
  2. you can notice that third column is some kind of summary, so adding “order by 3 asc" would effectively put “total" to the end of results because it has the highest value there:)
  3. third way involves a small trick. we can sort the results the way we want using functional order by.

first two options are easy. what about a functional order?

to simplify queries i created a table with data as shown by luckymurali_81:

create table lucky (
region text,
yday int4,
mtd int4,
target int4
);
copy lucky from stdin;
West 143469 2594373 100428960
Central 144034 2594109 100394040
Total 720490 12960302 502879290
South 143733 2588980 100303770
East 144344 2591332 101193660
North 144910 2591508 100558860
\.

now, let's try some “magic":

# select * from lucky order by (case when region = 'Total' THEN 1 ELSE 0 end);
region | yday | mtd | target
---------+--------+----------+-----------
West | 143469 | 2594373 | 100428960
Central | 144034 | 2594109 | 100394040
South | 143733 | 2588980 | 100303770
East | 144344 | 2591332 | 101193660
North | 144910 | 2591508 | 100558860
Total | 720490 | 12960302 | 502879290
(6 rows)

nice. but can we do it that regions will be sorted by name, but still keep total “down there"?

sure, we will use a nice addition to postgresql “order by .. nulls first/last":

# select * from lucky order by (case when region = 'Total' THEN NULL ELSE region end) ASC NULLS LAST;
region | yday | mtd | target
---------+--------+----------+-----------
Central | 144034 | 2594109 | 100394040
East | 144344 | 2591332 | 101193660
North | 144910 | 2591508 | 100558860
South | 143733 | 2588980 | 100303770
West | 143469 | 2594373 | 100428960
Total | 720490 | 12960302 | 502879290
(6 rows)

of course, this kind of functional order can be used in much more complicated scenarios. but this shows it's usability in quite simple way :)

  1. 8 comments

  2. Aug 29, 2007

    luckymurali_81 suggested another method:
    13:29 depesz, thanks
    13:30 despesz one more method
    13:31 select * from (youroriginalquery) as foo order by (region=’Total’),region

  3. Aug 29, 2007

    ouch – one bad news – order by “nulls first/last” is 8.3 only.
    i’m sorry for inconvenience – i’m using 8.3 on my lap, and didn’t check previous versions.
    so, on 8.2 the best way seems to be luckymurali_81 way with order by (condition), field;

  4. # xor
    Sep 1, 2007

    thanks depesz, i’m still waiting for 8.3 :)

  5. Sep 1, 2007

    @xor:
    yeah, i know. sorry – i am so used to using 8.3 that i use 8.3-isms out of habit.

  6. Jan 18, 2008

    nİCE BLOG. cONGRATS.

  7. Mar 18, 2008

    thanks.

  8. Dec 20, 2008

    I actually do the sorting by adding an index column, let’s say:

    select ‘W’,10 union select ‘A’,20 union select ‘T’,10+20

    I change to:

    select col1,col2 from (select ‘W’ as col1,10 as col2,1 as idx union select ‘A’,20,1 union select ‘T’,10+20,2) as t order by idx, col1

  1. 1 Trackback(s)

  2. Aug 31, 2007: Log Buffer #60: a Carnival of the Vanities for DBAs · Steve Karam · The Oracle Alchemist

Leave a comment