I'm sure someone will come up with a better solution, but this should
work:
SELECT BugNumber, COUNT(*) AS CrashCount
FROM Crashes
GROUP BY BugNumber
HAVING COUNT(*) = ( SELECT MAX(C.CrashCount)
FROM ( SELECT BugNumber, COUNT(*) AS
CrashCount
FROM Crashes
GROUP BY BugNumber
) C
)
Chris
[quoted text, click to view] rob.fulwell@gmail.com wrote:
> I have a query that calculates the number of times a given bug has been
> hit:
> SELECT Count(*) As CrashCount
> FROM Crashes
> WHERE (BugNumber = @UserBugNumber);
>
> Now I want to know which BugNumber would return the highest number for
> that query if that query iterated across all BugNumbers and what that
> BugNumber is.
>
> Thanks!
>
> Rob.
SELECT TOP 1 BugNumber, CrashCount
FROM (SELECT BugNumber, COUNT(*) AS CrashCount
FROM Crashes
GROUP BY BugNumber)
ORDER BY CrashCount DESC
[quoted text, click to view] On Nov 2, 10:28 am, rob.fulw...@gmail.com wrote:
> I have a query that calculates the number of times a given bug has been
> hit:
> SELECT Count(*) As CrashCount
> FROM Crashes
> WHERE (BugNumber = @UserBugNumber);
>
> Now I want to know which BugNumber would return the highest number for
> that query if that query iterated across all BugNumbers and what that
> BugNumber is.
>
> Thanks!
>
> Rob.
[quoted text, click to view] thomasroji@gmail.com wrote:
> SELECT TOP 1 BugNumber, CrashCount
> FROM (SELECT BugNumber, COUNT(*) AS CrashCount
> FROM Crashes
> GROUP BY BugNumber)
> ORDER BY CrashCount DESC
Or simply:
SELECT TOP 1 BugNumber, COUNT(*) AS CrashCount
FROM Crashes
GROUP BY BugNumber
ORDER BY COUNT(*) DESC
However this does not cater for the case where there are multiple
BugNumbers with the same maximum (not sure if that matters to the OP
though as it wasn't stated).
[quoted text, click to view] > However this does not cater for the case where there are multiple
> BugNumbers with the same maximum (not sure if that matters to the OP
> though as it wasn't stated).
In that case you can use..
SELECT TOP 1 WITH TIES ...
--
-Omnibuzz
http://omnibuzz-sql.blogspot.com/
[quoted text, click to view] Omnibuzz wrote:
> In that case you can use..
> SELECT TOP 1 WITH TIES ...
Cool... never seen that before!
Thanks to all for the timely replies. You saved my bacon!
Cheers,
Rob.
select top 1 BugNumber from
(select count(1) as crashcount, BugNumber from @Crashes group by id ) tmp
order by crashcount desc
Regards,
Augustin
http://augustinprasanna.blogspot.com [quoted text, click to view] <rob.fulwell@gmail.com> wrote in message
news:1162445280.793097.52550@m73g2000cwd.googlegroups.com...
>I have a query that calculates the number of times a given bug has been
> hit:
> SELECT Count(*) As CrashCount
> FROM Crashes
> WHERE (BugNumber = @UserBugNumber);
>
> Now I want to know which BugNumber would return the highest number for
> that query if that query iterated across all BugNumbers and what that
> BugNumber is.
>
> Thanks!
>
> Rob.
>
Don't see what you're looking for? Try a search.
