SQLTeam.com | Weblogs | Forums

What is really wrong with this query please?


#1

select [ContinentName], [EventName] from tblevent a join[dbo].[tblCountry] c
on a.[CountryID]=c.[CountryID] join [dbo].[tblContinent] b on c.[ContinentID]=b.[ContinentID]
where [ContinentName] in

(select top 3 [ContinentName] from tblevent a join[dbo].[tblCountry] c
on a.[CountryID]=c.[CountryID] join [dbo].[tblContinent] b on c.[ContinentID]=b.[ContinentID]
group by [ContinentName] ) order by count([EventName])

The query says error is Column 'dbo.tblContinent.ContinentName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


#2

Remove the order by clause

SELECT [ContinentName]
	,[EventName]
FROM tblevent a
INNER JOIN [dbo].[tblCountry] c
	ON a.[CountryID] = c.[CountryID]
INNER JOIN [dbo].[tblContinent] b
	ON c.[ContinentID] = b.[ContinentID]
WHERE [ContinentName] IN (
		SELECT TOP 3 [ContinentName]
		FROM tblevent a
		INNER JOIN [dbo].[tblCountry] c
			ON a.[CountryID] = c.[CountryID]
		INNER JOIN [dbo].[tblContinent] b
			ON c.[ContinentID] = b.[ContinentID]
		GROUP BY [ContinentName]
		)
-- ORDER BY count([EventName])