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.
| Author |
Topic |
|
grimmus
Yak Posting Veteran
53 Posts |
 Posted - 2008-11-10 : 16:08:44
|
| Hi,I have a reviews table with columns ReviewId,EstablishmentId,Rating,Text.The table contains 1000 records with reviews of about 80 different hotels.I am trying to get the top 10 hotels with the most reviews. So i want to count the number of occurences of a particular EstablishmentId.I know it's pretty simple but i am not sure of the best way to solve thisThanks in advance for any tips. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-10 : 16:11:42
|
| something like:select top 10 * from (select EstablishmentId, count(*)from ... group by EstablishmentId) t_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-11-10 : 16:24:01
|
don't forget to order by the count desc  |
|
|
|
andros30
Yak Posting Veteran
80 Posts |
Posted - 2008-11-10 : 16:24:18
|
| This is my take on this...select top 10 a.EstablishmentID, b.CountofEstIDfrom Reviews ainner join ( select EstablishmentID,count(*) as CountofEstID from Reviews group by EstablishmentID order by CountofEstID desc) bon b.EstablishmentID = a.EstablishmentIDorder by b.CountofEstID desc |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-11-10 : 16:28:37
|
| [code]select top 10 EstablishmentID, count(*) as CN from ...group by EstablishmentIDorder by count(*) desc[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-10 : 23:26:24
|
| [code]select distinct top 10 EstablishmentIDfrom ...order by count(ReviewId) over (partition by EstablishmentID) desc[/code] |
|
|
|
|
|
|
Count #reviews for each hotel