0

I'm getting this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

From this query:

(SELECT Z.dMonth,Z.dCount FROM (
 SELECT COUNT(*) dCount, month(tblDiving.date_of_diving) as dMonth
 FROM tblDiving
 GROUP BY month(tblDiving.date_of_diving) 
)Z
ORDER BY Z.dCount)

I understand I need to add "Top" Somewhere in this query, but where? In the end, this query need to return the Maximum dCount and the month of this dCount.

Edited:

This is my query:
select tblSite.name,tblSite.site_length,tblSite.site_depth,tblSite.water_type,tblSite.country,
COUNT(distinct tblDivingClub.number) as number_of_clubs,
COUNT(distinct tblDiving.diving_number) as number_of_divings,
COUNT(distinct tblDiving.guide) as number_of_guids,
sum(tblDiving.number_of_divers) as number_of_divers,
(SELECT top 1 Z.dMonth,Z.dCount FROM (
 SELECT COUNT(*) dCount, month(tblDiving.date_of_diving) as dMonth
FROM tblDiving 
GROUP BY month(tblDiving.date_of_diving) 
 )Z
ORDER BY Z.dCount)
from tblCountry inner join
tblSite on tblCountry.name=tblSite.country
inner join tblDiving on tblSite.name = tblDiving.diving_site
inner join tblDivingClub on tblDiving.diving_club = tblDivingClub.number
where tblSite.name in(select tblSite.name from tblSite where tblSite.country = 'Greece' ) and 
tblDiving.date_of_diving >= DATEADD(year,-1, GETDATE())
group by tblSite.name,tblSite.site_length,tblSite.site_depth,tblSite.water_type,tblSite.country

and now the error is: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Hila Gal
  • 149
  • 2
  • 9

3 Answers3

1

I think this should work

SELECT Z.dMonth
  ,Z.dCount
FROM (
SELECT COUNT(*) dCount
    ,month(tblDiving.date_of_diving) AS dMonth
FROM tblDiving
GROUP BY month(tblDiving.date_of_diving)
HAVING COUNT(*) = (
        SELECT MAX(A.Count)
        FROM (
            SELECT COUNT(*) Count
                ,tblDiving.date_of_diving
            FROM tblDiving
            GROUP BY tblDiving.date_of_diving
            ) A
        )
) Z
gunner11590
  • 26
  • 2
0

Why not just do:

SELECT TOP(1) COUNT(*) AS dCount, MONTH(tblDiving.date_of_diving) AS dMonth
FROM tblDiving
GROUP BY MONTH(tblDiving.date_of_diving)
ORDER BY dCount
Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
  • Because it's a subquery. I need to select from this query only the Maximum dCount, not all of the dCount's. – Hila Gal Aug 09 '14 at 18:28
  • Yes. But although I select top 1, I get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS – Hila Gal Aug 09 '14 at 18:34
0

Microsoft explains it here - http://support.microsoft.com/kb/841845

From the documentation -

"When you use an ORDER BY clause in a view, an inline function, a derived table, or a subquery, it does not guarantee ordered output. Instead, the ORDER BY clause is only used to guarantee that the result set that is generated by the Top operator has a consistent makeup. The ORDER BY clause only guarantees an ordered result set when it is specified in the outermost SELECT statement."

In your case, you can select the top 100 percent if you want to return everything, or just the top 1 if that's what you need.

(SELECT TOP 1 Z.dMonth,Z.dCount FROM (
 SELECT COUNT(*) dCount, month(tblDiving.date_of_diving) as dMonth
 FROM tblDiving
 GROUP BY month(tblDiving.date_of_diving) 
)Z
ORDER BY Z.dCount)
charlieparker
  • 186
  • 1
  • 8