[SQL] No sort with except

2012-03-01 Thread reto . buchli
Dear all, When I run the following SQL with PostgreSQL 9.1: -- SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status FROM person WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') ORDER BY pernr, eindt DESC; -- it wo

Re: [SQL] No sort with except

2012-03-01 Thread Frank Lanitz
Am 01.03.2012 09:13, schrieb reto.buc...@wsl.ch: > Dear all, > > When I run the following SQL with PostgreSQL 9.1: > > -- > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status > FROM person > > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD') > AND ausdt

Re: [SQL] No sort with except

2012-03-01 Thread reto . buchli
pgsql-sql-ow...@postgresql.org schrieb am 01.03.2012 09:16:53: > From: Frank Lanitz > To: pgsql-sql@postgresql.org, > Date: 01.03.2012 09:16 > Subject: Re: [SQL] No sort with except > Sent by: pgsql-sql-ow...@postgresql.org > > Am 01.03.2012 09:13, schrieb reto.buc...@wsl.ch: > > Dear all, > >

Re: [SQL] No sort with except

2012-03-01 Thread Philip Couling
Hi Reto You are right to assume that you're query is ordering the second select and not the whole query. To order the query as a whole it in parentheses and put the ORDER BY at the end: ( SELECT foo FROM X EXCEPT SELECT foo FROM Y ) ORDER BY foo; Hope this helps On 01/03/2012 08:56, reto.buc.

Re: [SQL] No sort with except

2012-03-01 Thread Jasen Betts
On 2012-03-01, reto.buc...@wsl.ch wrote: > Dies ist eine mehrteilige Nachricht im MIME-Format. > --=_alternative 002D2CF5C12579B4_= > Content-Type: text/plain; charset="US-ASCII" > > Dear all, > > When I run the following SQL with PostgreSQL 9.1: > > -- > SELECT DISTINCT ON (pernr) pernr, vorna, n

[SQL] Aggregate and join problem

2012-03-01 Thread Swärd Mårten
Hi folks I have some troubles to create a SQL-query and my hope is that someone of you could help me with this.. It's somewhat difficult to explain what I want to do but I'll give it a try and see if you can understand the problem.. Ahh fuck this.. It's almost imposible to explain.. :) I don't u

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Oliveiros d'Azevedo Cristina
Hi, Swärd, As you didn't name your tables' columns I decided to call them col1, col2, etc. I dunno if this will do what you want as it is completely untested code. But, give it a try and see if it works and if it doesn't, tell me the error, and we'll continue from there. You'll have to substitu

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Swärd Mårten
It worked like a charm! Many thanks for that great solution! Best regards, Mårten Från: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] Skickat: den 1 mars 2012 11:49 Till: Swärd Mårten; pgsql-sql@postgresql.org Ämne: Re: [SQL] Aggregate and join problem Hi, Swärd, As you di

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Oliveiros d'Azevedo Cristina
Great to Hear! Best, Oliver - Original Message - From: Swärd Mårten To: Oliveiros d'Azevedo Cristina ; pgsql-sql@postgresql.org Sent: Thursday, March 01, 2012 11:01 AM Subject: Re: [SQL] Aggregate and join problem It worked like a charm! Many thanks for that great solution

[SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens
I have a database that I must assign ownership to a new role. I want this new role to own the entire database and all of it's tables, views, triggers, & all. When I run the ALTER DATABASE command below, it only changes the database role but the tables are all still owned by the previous role. Is th

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens
On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang wrote: > Hi > You can try this command "REASSIGN OWNED BY TO ..." like this: > REASSIGN OWNED BY previous_role TO new_role; >  DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 2

Re: [SQL] Natural sort order

2012-03-01 Thread F. BROUARD / SQLpro
The fastest way is to create a ref table with all possible entries, ordered with an additionnal numerical column, indexing it and make a join from your table to this ref table. A + Le 17/12/2011 11:33, Richard Klingler a écrit : Morning... What is the fastest way to achieve natural ordering

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Adrian Klaver
On 03/01/2012 09:04 AM, Carlos Mennens wrote: On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang wrote: Hi You can try this command "REASSIGN OWNED BY TO ..." like this: REASSIGN OWNED BY previous_role TO new_role; DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL con

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens
I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens wrote: > I did do a Google search for "PostgreSQL 9.1 change ownership > recursively" but either couldn't find what I was lookin

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Adrian Klaver
On 03/01/2012 11:37 AM, Carlos Mennens wrote: I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! In your previous post my guess is this: iamunix=# \c postgres was really meant to be: iamunix=# \c - postgres The first chan