> -----Original Message----- > From: ajmcello [mailto:ajmcello78@xxxxxxxxx] > Sent: Monday, July 02, 2012 8:26 PM > To: David Johnston > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: 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@xxxxxxxxx> > wrote: > >> -----Original Message----- > >> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > >> owner@xxxxxxxxxxxxxx] On Behalf Of ajmcello > >> Sent: Monday, July 02, 2012 7:23 PM > >> To: pgsql-general@xxxxxxxxxxxxxx > >> Subject: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
