Re: Query ordering question

> -----Original Message-----
> From: ajmcello [mailto:ajmcello78(at)gmail(dot)com]
> Sent: Monday, July 02, 2012 8:26 PM
> To: David Johnston
> Cc: pgsql-general(at)postgresql(dot)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(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.
> >
> >
> >

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