return max counted result. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

return max counted result.

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-09 : 09:57:15

hey friends
With sql I now understand the count, max, min and sum function

but if i am returning a count
what having or where clause do i write if only want to return the largest number (the highest counted result)

so my uncles wants me to return one row with the highest number of things counted.
cheers

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-09 : 10:07:23

There is many options to limit number of rows returned. I prefer to use TOP if I can, and if I can't I go for ranking functions like ROW_NUMBER.
So to sum up if you want one row then you can use TOP(1) like this:


SELECT TOP(1) x, COUNT(y) AS y_count
  FROM A
 GROUP BY x
 ORDER BY y_count DESC

So here we are getting the row with highest y count.
If your requirement not suited by this, then you can provide me with more information to be able to rewrite it as required.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 16:33:11

quote:
There is many options to limit number of rows returned

My favorite way is:

SELECT *
FROM table_name t1
WHERE NOT EXISTS
      (SELECT NULL
        FROM table_name t2
        WHERE t2.x = t1.x
        AND t2.y > t1.y);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources