[GENERAL] Do I need archive_mode = on for hot standby?
Hi there. I'm planning on setting up a master database and multiple hot standby slaves using streaming replication. If I use a large(*) value on wal_keep_segments do I really need archive_mode = on then? Any potential problems with this strategy I should be aware about? (*) With large value I mean a value such that a failed slave will be up and running again (with a few days margin) before the master server starts to clean out old WAL segments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Slony-I Replication
On Sun, Jul 1, 2012 at 10:18 PM, Iqbal Aroussi wrote: > Hi dear friends, > > This my first post to PostgreSQL mailing list. > > I'm trying to setup a master-slave replication with PostgreSQL 9.1 / > Slony-I, this is first time I'm doing it and I'm kind of lost :( > Is there any tutorial explaining the steps how to do it with *FreeBSD* ? > > I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 / > slony1v2-2.1.1 > > Thanks a lot in advance > > Below URL's provides more information about slony Replication setup: http://raghavt.blogspot.in/2012/07/simple-slony-i-replication-setup.html Thanks & Regards, Raghu Ram
Re: [GENERAL] PostgreSQL Slony-I Replication
Iqbal Aroussi wrote: > I'm trying to setup a master-slave replication with PostgreSQL 9.1 / Slony-I, this is first time I'm > doing it and I'm kind of lost :( > Is there any tutorial explaining the steps how to do it with FreeBSD ? > > I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 / slony1v2-2.1.1 I'd ask the Slony lists: http://lists.slony.info/mailman/listinfo Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Slony-I Replication
On Mon, Jul 2, 2012 at 9:32 AM, raghu ram wrote: > > > On Sun, Jul 1, 2012 at 10:18 PM, Iqbal Aroussi wrote: > >> Hi dear friends, >> >> This my first post to PostgreSQL mailing list. >> >> I'm trying to setup a master-slave replication with PostgreSQL 9.1 / >> Slony-I, this is first time I'm doing it and I'm kind of lost :( >> Is there any tutorial explaining the steps how to do it with *FreeBSD* ? >> >> I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 / >> slony1v2-2.1.1 >> >> Thanks a lot in advance >> >> > Below URL's provides more information about slony Replication setup: > > http://raghavt.blogspot.in/2012/07/simple-slony-i-replication-setup.html > > > > Thanks & Regards, > > Raghu Ram > Hi Raghu, Thanks a lot for the you provided. It's exactly what I was looking for. Best Regards Iqbal A.
Re: [GENERAL] PostgreSQL Slony-I Replication
On Mon, Jul 2, 2012 at 9:43 AM, Albe Laurenz wrote: > Iqbal Aroussi wrote: > > I'm trying to setup a master-slave replication with PostgreSQL 9.1 / > Slony-I, this is first time I'm > > doing it and I'm kind of lost :( > > Is there any tutorial explaining the steps how to do it with FreeBSD ? > > > > I'm using FreeBSD db1 9.0-RELEASE / postgresql-server-9.1.4 / > slony1v2-2.1.1 > > I'd ask the Slony lists: > http://lists.slony.info/mailman/listinfo > > Yours, > Laurenz Albe > Hi Laurenz. Thanks a lot for your advice, I'll do if I still need it. Best Regards Iqbal A.
[GENERAL] Is there a way to recover deleted records if database has not been vacuumed?
I am using Django to develop an app and I think I must have done a syncdb (which deletes all records) without realizing it. I have not vacuumed that database and I have also made a copy of the data directory. Is there some way to recover the deleted records? -- Frank Church === http://devblog.brahmancreations.com
Re: [GENERAL] Is there a way to recover deleted records if database has not been vacuumed?
On Mon, Jul 2, 2012 at 3:21 PM, Frank Church wrote: > > I am using Django to develop an app and I think I must have done a syncdb > (which deletes all records) without realizing it. > > I have not vacuumed that database and I have also made a copy of the data > directory. > > Is there some way to recover the deleted records? > > You can perform Point in time recovery to recover the deleted records,if you have enabled database in WAL archiving mode. Below URL provides more information of the Point-in-time recovery on PostgreSQL http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/ Thanks & Regards, Raghu Ram
Re: [GENERAL] is there any query so that I may find the list of columns those have rules?
AI Rumman wrote: > I am getting the following error: > ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE numeric(10,6); > ERROR: cannot alter type of a column used by a view or rule > DETAIL: rule _RETURN on view master_view depends on column "base_table_field1" > > I know that I have a view based in the "base_table_field1" column. > > So is there any query so that I may find the list of columns those have rules? Does this help: SELECT d1.refobjid::regclass AS table, a.attname AS column, d2.refobjid::regclass AS "referenced by view" FROM pg_depend d1 JOIN pg_depend d2 ON (d1.objid = d2.objid AND d1.classid = 'pg_rewrite'::regclass AND d1.refclassid ='pg_class'::regclass AND d2.classid = 'pg_rewrite'::regclass AND d2.refclassid = 'pg_class'::regclass AND d2.deptype='i') JOIN pg_attribute a ON (d1.refobjid = a.attrelid AND d1.refobjsubid = a.attnum) WHERE d1.refobjid='base_table'::regclass ORDER BY 1, 2, 3; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a way to recover deleted records if database has not been vacuumed?
On Mon, Jul 02, 2012 at 10:51:33AM +0100, Frank Church wrote: > I am using Django to develop an app and I think I must have done a syncdb > (which deletes all records) without realizing it. > I have not vacuumed that database and I have also made a copy of the data > directory. > Is there some way to recover the deleted records? check this - maybe it can help: http://www.depesz.com/2012/04/04/lets-talk-dirty/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
[Please don't top-post. http://www.idallen.com/topposting.html ] tuanhoanganh wrote: > Kevin Grittner > tuanhoanganh wrote: >>> VACUUM ANALYZE pg_catalog.pg_attribute, pg_catalog.pg_type, >>> pg_catalog.pg_depend run all time of day. >> What are the results of running the query on this page?: >> >> http://wiki.postgresql.org/wiki/Server_Configuration > Here is my config > "version";"PostgreSQL 9.0.6, compiled by Visual C++ build 1500, > 64-bit" If you can, an update would be good. http://www.postgresql.org/support/versioning/ That said, I don't see any particular bug fixes that would be related. > "autovacuum_analyze_threshold";"250" > "autovacuum_vacuum_threshold";"1000" My experience is that setting these smaller helps autovacuum keep small tables from bloating, reducing the overall work done by autovacuum and improving overall performance. This *might* be a contributing factor to you issue. > "enable_seqscan";"off" This is a very bad idea. A seqscan if often the fastest way to get a set of data. If seqscans where being chosen when some other technique actually was faster, you probably have a heavily cached active data set, and would do much better to turn this back on and set: seq_page_cost = 0.1 random_page_cost = 0.1 cpu_tuple_cost = 0.03 I have found these settings to work well with my data and workloads, you may need to make adjustments to find the best settings for your environment. Again, this doesn't seem related to your question, but if you are cncerned about performance, it should help. > "shared_buffers";"2GB" Benchmarks have shown that 16MB generally helps performance. >> How is overall performance on the machine? It would help to know whether the autovacuum processes you are seeing are actually using a lot of CPU or disk IO. Do they reappear after a restart? Are these processes involved in any blocking (ni sp_locks)? -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute
"Kevin Grittner" wrote: >> "shared_buffers";"2GB" > > Benchmarks have shown that 16MB generally helps performance. Yikes! Editing error there -- I meant to put that comment under the wal_buffers setting. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 >= '100' 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query ordering question
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@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 > >= '100' 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 wrote: >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@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 >> >= '100' 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 >
Re: [GENERAL] Query ordering question
> -Original Message- > From: ajmcello [mailto:ajmcell...@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 > wrote: > >> -Original Message- > >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > >> ow...@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 > >> >= '100' 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 nam
Re: [GENERAL] Query ordering question
On 3 Jul 2012, at 1:22, ajmcello wrote: > db=# SELECT name,date,percent,price,time,amount FROM name WHERE amount >= > '100' 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 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 numbers have 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
