Thread: 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
> -----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...:) 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. > > >
> -----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 percentDESC; > > 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 numbershave 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.