Re: Query ordering question

Thanks for the response. I'm working with it, but it seems to sort
everything by oldest time first, and the name column isn't sorted by
name with the highest percent first with the latest (newest) time
first.

Basically, I want the query to display the newest name with the newest
time with the highest percent first, with all other records of that
name to follow that name. Then move onto the next name and do the same
thing.

Its kind of complicated for me to explain...:)

On Mon, Jul 2, 2012 at 5:11 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of ajmcello
>> Sent: Monday, July 02, 2012 7:23 PM
>> To: pgsql-general(at)postgresql(dot)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.
>
>
>