> -----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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
