Thread: Ambiguous columns
If I have two tables(taba, tabb) with the same column (column1) name and
try to do:
SELECT taba.column1 AS column1,tabb.column2 AS column2
FROM taba AS a
JOIN tabb AS b
ON taba.indexCol = tabb.indexCol
GROUP BY column1
It tells me that column1 is ambiguous. Is that to be expected? I can
semi see why - but I thought you could only only group on selected
fields, and the only 'selected' column1 is that from taba. Minor issue,
with easy work around.
--
Regards,
Steve Tucknott
ReTSol Ltd
DDI 01903 828769
MOBILE 07736715772
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com
am 03.07.2005, um 8:17:01 +0100 mailte Steve Tucknott folgendes: > If I have two tables(taba, tabb) with the same column (column1) name and > try to do: > > SELECT taba.column1 AS column1,tabb.column2 AS column2 > FROM taba AS a > JOIN tabb AS b > ON taba.indexCol = tabb.indexCol > GROUP BY column1 > > It tells me that column1 is ambiguous. Is that to be expected? I can untestet: SELECT a.column1 AS column1,b.column2 AS column2 FROM taba a JOIN tabb b ON a.indexCol = b.indexCol GROUP BY a.column1 Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
Steve Tucknott <steve@retsol.co.uk> writes:
> If I have two tables(taba, tabb) with the same column (column1) name and
> try to do:
> SELECT taba.column1 AS column1,tabb.column2 AS column2
> FROM taba AS a
> JOIN tabb AS b
> ON taba.indexCol = tabb.indexCol
> GROUP BY column1
> It tells me that column1 is ambiguous. Is that to be expected?
Yes.
> I thought you could only only group on selected fields,
No, that's never been true. You're confusing it with ORDER BY,
which has different rules.
regards, tom lane
On Sun, Jul 03, 2005 at 08:17:01 +0100, Steve Tucknott <steve@retsol.co.uk> wrote: > If I have two tables(taba, tabb) with the same column (column1) name and > try to do: > > SELECT taba.column1 AS column1,tabb.column2 AS column2 > FROM taba AS a > JOIN tabb AS b > ON taba.indexCol = tabb.indexCol > GROUP BY column1 > > It tells me that column1 is ambiguous. Is that to be expected? I can > semi see why - but I thought you could only only group on selected > fields, and the only 'selected' column1 is that from taba. Minor issue, > with easy work around. You have things backwards. You can only select fields that are used in the group by list or that are aggregates. You can have things used for grouping that are not output in the select list.
Tom, That could have been the case. The original query had both GROUP and ORDER. I assumed (my mistake) that the error was being generated by the GROUP clause. Why should ORDER by object - isn't that the same - ie it can only order on the selected fields? On Sun, 2005-07-03 at 17:07, Tom Lane wrote: > Steve Tucknott <steve@retsol.co.uk> writes: > > If I have two tables(taba, tabb) with the same column (column1) name and > > try to do: > > > SELECT taba.column1 AS column1,tabb.column2 AS column2 > > FROM taba AS a > > JOIN tabb AS b > > ON taba.indexCol = tabb.indexCol > > GROUP BY column1 > > > It tells me that column1 is ambiguous. Is that to be expected? > > Yes. > > > I thought you could only only group on selected fields, > > No, that's never been true. You're confusing it with ORDER BY, > which has different rules. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Regards, Steve Tucknott ReTSol Ltd DDI 01903 828769 MOBILE 07736715772 ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
Steve Tucknott wrote: >If I have two tables(taba, tabb) with the same column (column1) name and >try to do: > >SELECT taba.column1 AS column1,tabb.column2 AS column2 > FROM taba AS a > JOIN tabb AS b > ON taba.indexCol = tabb.indexCol >GROUP BY column1 > >It tells me that column1 is ambiguous. Is that to be expected? I can >semi see why - but I thought you could only only group on selected >fields, and the only 'selected' column1 is that from taba. Minor issue, >with easy work around. > > Oracle has a fun bug in this case when it picks which table/column to do the group by on randomly. Or it uses some kind of crazy psychic power to determine which table you DON'T mean, and use that. Jeff