Re: Query ordering question - Mailing list pgsql-general

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