Re: [GENERAL] A select DISTINCT query? - followup Q
In article [EMAIL PROTECTED], Phil Rhoades [EMAIL PROTECTED] writes: People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment If you want to select both columns, but have uniqueness over the first only, you can use a derived table: SELECT tbl.name, tbl.comment FROM tbl JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t ON t.name = tbl.name ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A select DISTINCT query? - followup Q
Harald Fuchs [EMAIL PROTECTED] writes: If you want to select both columns, but have uniqueness over the first only, you can use a derived table: SELECT tbl.name, tbl.comment FROM tbl JOIN (SELECT name FROM tbl GROUP BY name HAVING count(*) = 1) AS t ON t.name = tbl.name Or use the first() aggregate since you know there's only going to be one anyways: select name, first(comment) from tbl group by name having count(*) = 1 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A select DISTINCT query? - followup Q
Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Phil Rhoades wrote: People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. Mike Ginsburg Collaborative Fusion, Inc. [EMAIL PROTECTED] 412-422-3463 x4015 -- IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] A select DISTINCT query? - followup Q
On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote: Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Is the requirement of select fields matching group by fields a SQL92 requirement or something to due to Postgres? I ask because with Visual Fox Pro I know that I can have several select fields with only one group by field. -- John Fabiani ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
johnf [EMAIL PROTECTED] writes: On Sunday 27 January 2008 10:56:18 am Mike Ginsburg wrote: Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. Is the requirement of select fields matching group by fields a SQL92 requirement or something to due to Postgres? I ask because with Visual Fox Pro I know that I can have several select fields with only one group by field. It is in fact a SQL92 requirement: section 7.9 query specification saith 7) If T is a grouped table, then each column reference in each value expression that references a column of T shall refer- ence a grouping column or be specified within a set function specification. (A set function is what PG calls an aggregate function.) Later versions of the spec relax that a bit: in SQL99, if you GROUP BY a primary key (or some other cases that are not too interesting in practice) then there can be only one row per group anyway and so references to other columns will have well-defined values. We have not got around to implementing that extension. I don't know FoxPro, but there are some DBMSes (cough m***l cough) that simply let you reference ungrouped columns without any check to see whether what you have written is sensible or not. The results you get from such a query are pretty unpredictable, or at least implementation- dependent. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A select DISTINCT query? - followup Q
Mike, I can't do that with my comments - I get all six of the records in the result with the example instead of just four like I want . . but someone else had a solution without using the group by clause . . Phil. On Sun, 2008-01-27 at 13:56 -0500, Mike Ginsburg wrote: Hi Phil, Each of columns that you specify in your SELECT clause, must also appear in the GROPU BY clause. SELECT COUNT(*) AS cnt, name, comment, ... FROM tst GROUP BY name, comment, ... HAVING COUNT(*) = 1; Phil Rhoades wrote: People, select count(*) as cnt, name from tst group by name having count(*) = 1 This worked for my basic example but not for my actual problem - I get column comment must appear in the GROUP BY clause or be used in an aggregate function errors so I have a related question: With table: name comment 1first comment 2second comment 3third comment 3fourth comment 4fifth comment 5sixth comment - how can I use something like the previous select statement but where the comment field does not appear in the group by clause and gives the following result: 1first comment 2second comment 4fifth comment 5sixth comment Thanks, Phil. Mike Ginsburg Collaborative Fusion, Inc. [EMAIL PROTECTED] 412-422-3463 x4015 -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
