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.

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...:)

-----Original Message-----
From: ajmcello [mailto:ajmcello78@gmail.com]
Sent: Monday, July 02, 2012 8:26 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@yahoo.com>
wrote:

-----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.

WITH
most_recent_names AS ( -- so we first determine the time component of the
most recent record for each name
SELECT name, max(date+time) AS latest_time FROM table GROUP BY name
)
, most_recent_name_percent AS ( --for those records we join in the
corresponding percentage percent
SELECT name, latest_time, percent FROM most_recent_names JOIN table
ON (name = name, latest_time = (date+time))
)
, ordered_names AS ( --then we rank the names based upon those percentages
SELECT name, latest_time, percent, ROW_NUMBER() OVER (PARTITION BY
name ORDER BY percent DESC) AS name_rank FROM most_recent_name_percent
)
, extended_name_info AS ( --lastly we add in all additional records, using
the ranking of the most recent percentages to sort them initially and then
adding whatever sub-level sorted we need
SELECT name, latest_time, percent, name_rank, table.* FROM
ordered_name JOIN table ON (name = name, latest_time = (date+time))
ORDER BY name_name, other_fields...
)
SELECT * FROM name_info

On 3 Jul 2012, at 1:22, ajmcello wrote:

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

This output makes no sense with the sort order you specified.
Are you sure that the output matches the query? If it does, is percent a numeric field or is it a varchar where the numbers have varying amount of leading space perhaps?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.