On Fri, 27 Feb 2004, Bill Moran wrote: > Hey all. > > I've hit an SQL problem that I'm a bit mystified by. I have two different > questions regarding this problem: why? and how do I work around it? > > The following query: > > SELECT GCP.id, > GCP.Name > FROM Gov_Capital_Project GCP, > WHERE TLM.TLI_ID = $2 > group by GCP.id > ORDER BY gcp.name; > > Produces the following error: > > ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an > aggregate function
OK, let's look at a test table: id | data --------- 0 | 'abc' 0 | 'def' 1 | 'ghi' Now, let's use this query: select id, data from test_table group by id; what results should I get back? I have two possible results for the data column, abc and def. But I only get one row with a 0 in it, so which one of those do I pick? If I use an aggregate I can be sure to get the first or last one: select id, max(data) from test_table group by id; Also, you may want to look at postgresql's extension, "distinct on": http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-DISTINCT It can give you the kind of results you want. select distinct on (id) id, data from test_table; But is know to be indeterminate, so you may get different results each time. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
