From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | john snow <ofbizfanster(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: group by rollup and cube |
Date: | 2017-11-16 07:27:27 |
Message-ID: | 1510817247.2639.20.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
john snow wrote:
> i'm trying to learn how to do these from these examples that i found in:
>
> https://www.databasejournal.com/features/mssql/using-the-rollup-cube-and-grouping-sets-operators.html
>
> after modifying the code to run in postgresql 10, i'm finding that even though
> i'm getting subtotal and grand total values same as those in the article,
> i'm not quite getting the report rows in the desired orders.
A query result has no guaranteed ordering unless you specify
one with an ORDER BY clause.
ORDER BY is executed after the grouping, so you can use it to
sort the result rows.
You'll have to come up with an ORDER BY clause that puts the
result in the desired order.
The NULLS LAST clause will help in some of your cases.
For example, for your first question I'd use
ORDER BY type NULLS LAST
to put the summary line at the bottom.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Montgomery | 2017-11-17 22:52:54 | ERROR: expected one dependency record for TOAST table, found 0 |
Previous Message | Laurenz Albe | 2017-11-16 07:21:09 | Re: How to get n records from parent table and their children |