Re: Query ordering question

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of ajmcello
> Sent: Monday, July 02, 2012 7:23 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Query ordering question
>
> I'm interested in sorting my query by time descending, with the highest
> percent by latest time shown first, and then every other record associated
> with column name sorted by time descending, following the first record.
> Does that make sense?
>
> The first query is the best I've come up with. The second, is how I'd like
it
> took.
>
> Any suggestions?
>
> Thanks in advance.
>
> db=# SELECT name,date,percent,price,time,amount FROM name WHERE
> amount
> >= '1000000' AND date='$today' ORDER BY percent DESC;
>
>  name |    date     | percent        |  price  |   time   |  amount
> --------+------------+----------------+---------+----------+----------
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 14:59:00 | 26975372
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 15:01:00 | 27001372
>  BOB   | 2012-07-02 |          64.06 |  8.8755 | 15:04:00 | 27145552
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 14:57:00 | 26946338
>  GIL   | 2012-07-02 |          38.72 |  0.6789 | 14:55:00 |  1012880
>  GIL   | 2012-07-02 |          38.74 |   0.679 | 14:59:00 |  1026190
>  GIL   | 2012-07-02 |          38.74 |   0.679 | 14:57:00 |  1017480
>  GIL   | 2012-07-02 |          38.95 |    0.68 | 15:01:00 |  1027590
>  BUB   | 2012-07-02 |           22.8 |  4.1262 | 14:57:00 |  7300694
>  BUB   | 2012-07-02 |          22.02 |     4.1 | 14:59:00 |  7346968
>  BUB   | 2012-07-02 |          22.47 |   4.115 | 15:01:00 |  7371456
>  BUB   | 2012-07-02 |          22.02 |     4.1 | 15:04:00 |  7406511
>  BUB   | 2012-07-02 |          23.84 |  4.1611 | 14:55:00 |  7221402
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:02:00 | 71965920
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:05:00 | 71986544
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:00:00 | 71943280
>  SAL   | 2012-07-02 |          21.52 |   41.34 | 14:55:00 | 71903096
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 14:58:00 | 71921496
>
>  name |    date     | percent        |  price  |   time   |  amount
> --------+------------+----------------+---------+----------+----------
>  BOB   | 2012-07-02 |          64.06 |  8.8755 | 15:04:00 | 27145552
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 15:01:00 | 27001372
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 14:59:00 | 26975372
>  BOB   | 2012-07-02 |          63.77 |    8.86 | 14:57:00 | 26946338
>  BOB   | 2012-07-02 |          63.96 |    8.87 | 14:55:00 | 26935038
>  GIL   | 2012-07-02 |          38.95 |    0.68 | 15:01:00 |  1027590
>  GIL   | 2012-07-02 |          38.74 |   0.679 | 14:59:00 |  1026190
>  GIL   | 2012-07-02 |          38.74 |   0.679 | 14:57:00 |  1017480
>  GIL   | 2012-07-02 |          38.72 |  0.6789 | 14:55:00 |  1012880
>  BUB   | 2012-07-02 |          22.02 |     4.1 | 15:04:00 |  7406511
>  BUB   | 2012-07-02 |          22.47 |   4.115 | 15:01:00 |  7371456
>  BUB   | 2012-07-02 |          22.02 |     4.1 | 14:59:00 |  7346968
>  BUB   | 2012-07-02 |           22.8 |  4.1262 | 14:57:00 |  7300694
>  BUB   | 2012-07-02 |          23.84 |  4.1611 | 14:55:00 |  7221402
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:05:00 | 71986544
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:02:00 | 71965920
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 15:00:00 | 71943280
>  SAL   | 2012-07-02 |           21.5 |  41.335 | 14:58:00 | 71921496
>  SAL   | 2012-07-02 |          21.52 |   41.34 | 14:55:00 | 71903096
>

Try this:

WITH first_row_of_group AS (
SELECT name, max_percent, ROW_NUMBER() OVER (PARTITION BY name ORDER BY
max_percent DESC) AS group_rank
FROM (SELECT name, max(percent) AS max_percent FROM table GROUP BY name )
first_record
)
SELECT name, max_percent, percent, date, time
FROM first_row_of_group
JOIN table USING (name)
ORDER BY group_rank, date, time

Basically you have to determine the order of the bigger group items first
(names in order of maximum percentage) and then join this to the original
dataset keeping the group order intact and adding in the time sorting
component.

You haven't given quite enough information to guarantee that this will work
without modification but it should at least get you started.  You are going
to require a sub-select since you are sorting on two distinctly different
levels of attributes (name by percentage, detail by time).

David J.