Urgent!Please help: TOP 3 cities only returned based on count() – result

pts.
Tags:
Microsoft SQL Server 2000
SQL
SQL Server
I have three tables: Country, City, Restaurant. I would like to have a query that first calculates how many restaurants are for each city. I would only want to return those three cities for each country which has the most number of restaurants. Country-table: countryid, countryname City-table: cityid, cityname Restaurant-table: restaurantid cityid countryid isactive Only active ones should be counted (isactive=1) I have tried everything I can think of, but I do not get correct list. Please, someone help! the basic goes likes this: select co.countryname, ci.cityname, count(r.restaurantid) from restaurant r join country co on r.countryid=co.countryid join city ci on r.cityid=ci.cityid where r.isactive=1 group by co.countryname, ci.cityname order by co.countryname, count(r.restaurantid) desc, ci.cityname I have used that as a base to this and then tried everything in my power to have only the top 3 cities returned, but no success!

Answer Wiki

Thanks. We'll let you know when a new response is added.

You don’t indicate whether your database is Oracle, SQL Server, DB2, Informix or something else. Oracle has had analytic functions (starting with 8i) which might be the easiest way to solve this. Is your database Oracle 8i, 9i or 10g?

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Timallard
    You need a TOP(3) statement that only applies to resturants ... not tested: SELECT TOP(3) resturantid, co.countryid, ci.cityid FROM r resturant WHERE r.isactive=1 JOIN co country ON r.countryid = co.countryid JOIN ci city ON r.cityid = ci.cityid GROUP BY co.countryname, ci.cityname ORDER BY co.countryname, resturantid, ci.cityid DESC
    0 pointsBadges:
    report
  • SloopJohnB
    Have you tried SELECT TOP 3 ... HTH, John Barone
    0 pointsBadges:
    report
  • Rolaaus
    You can embed an SQL statement into another statement, and I am not exactly 100% sure of the syntax. You would want the embedded statement to pull the 3 city ID's with the highest COUNT of resturauntID's. From there, you could somehow (and here is where I forget the proper syntax) do a join to this query with the simple query of city/country. Something like select cityname, cityid from city-table where cityid = (select county(esturauntid), cityid from resturaunt-table TOP 3) Actually, the problem I see with this is that there will never be a matched join because you with get a count of resturaunts and the cityid will never be equal with what you are wanting, but there should be a way to get the rusturaunt count without 'displaying' it in the sub-query, but again, I forget how to do that.
    0 pointsBadges:
    report
  • Stevewaltz
    I hope TOP worked for you. If not, I have a variation on r937's solution that works with a similar situation I have in one of my Oracle databases. That is, if SQL server has something like Oracle's rownum psuedocolumn (returns a sequential number starting with 1 for each row of a cursor). If so, regular_registration, county and location_code are analogous to your restaurant, country and city tables. The advantage is that it can be easily adjusted to choose the top 2 or 5 or any other number of cities without lengthening the code or increasing the processing appreciably. Hope you don't need it or the translation to SQl Server and your tables is not to difficult. select c.county_name, l.city_name, y.registrations from location_code l, entpr.county c, ( select county_code, min(row_num) min_row from ( select c.county_code, r.city_code, r.registrations, rownum row_num from ( select county_code, city_code, count(*) registrations from regular_registration where transaction_id is not null group by county_code, city_code) r, county c where c.county_code = r.county_code order by c.county_code, r.city_code, r.registrations desc) group by county_code) x, ( select c.county_code, r.city_code, r.registrations, rownum row_num from ( select county_code, city_code, count(*) registrations from regular_registration where transaction_id is not null group by county_code, city_code) r, county c where c.county_code = r.county_code order by c.county_code, r.city_code, r.registrations desc) y where x.county_code = y.county_code and y.row_num - x.min_row
    0 pointsBadges:
    report
  • catalin67
    From what I see you need to: - count the restaurants for each city - for each country display first top 3 cities You should use a WHILE statement or an EXIST clause to force a nested query (to get top 3 cities) to run for each country you want.
    0 pointsBadges:
    report
  • Ronjar
    there is no WHILE in SQL ;o) you guys keep forgetting that ronjar did not mention which database he or she is using therefore, in my opinion (with which you may freely disagree), you should not propose non-standard solutions involving proprietary syntax like TOP or ROWNUM my solution is standard SQL only
    0 pointsBadges:
    report
  • Julius
    If you use MS SQL Server, maybe you can try this: select co.countryname, ci.cityname, r.restaurantcount from city as ci inner join (select top 3 countryid, cityid, count(*) as restaurantcount from restaurant where isactive = 1 group by countryid, cityid order by count(*) desc) as r on ci.cityid = r.cityid inner join country as co on r.countryid = co.countryid Hope it's work.
    0 pointsBadges:
    report
  • Ronjar
    Hi, Thanks to you all so far! I have been testing all those queries you have sent except the last one from Julius. At the moment I'm not able to access to the server but I will test it as soon as possibe. Sorry btw for the delay in this answer, whole easter has been busy! Those queries have not given the correct result but they have also given some ideas on how to proceed. Let's see how this goes tomorrow! I use Ms Sql Server btw. Thanks, Ronjar
    0 pointsBadges:
    report
  • Ronjar
    the fact that this discussion board does not allow code formatting is really a huge p.i.t.a. create view citycounts as select co.countryname , ci.cityname , count(r.restaurantid) as restaurants from restaurant r inner join country co on r.countryid=co.countryid inner join city ci on r.cityid=ci.cityid where r.isactive=1 group by co.countryname , ci.cityname select countryname , cityname , restaurants from citycounts as x where restaurants in ( select top 3 restaurants from citycounts where countryname = x.countryname )
    0 pointsBadges:
    report
  • MFleming
    I think I have it for you, tested in DB2 zOS platform. Give this a try: -- Gives list ranked by Country-City-restaurant count -- Ordered by country-rank-city -- SELECT P1.COUNTRYNAME,P1.CITYNAME , P1.REST_CNT , P3.REST_CNT AS RANK FROM ( SELECT CO.COUNTRYID , CO.COUNTRYNAME , CI.CITYID , CI.CITYNAME , COUNT(R.RESTAURANTID) AS REST_CNT FROM RESTAURANT R JOIN COUNTRY CO ON R.COUNTRYID=CO.COUNTRYID JOIN CITY CI ON R.CITYID=CI.CITYID WHERE R.ISACTIVE=1 GROUP BY CO.COUNTRYID , CO.COUNTRYNAME , CI.CITYID , CI.CITYNAME ) P1 , TABLE ( SELECT COUNT(DISTINCT P2.REST_CNT) AS REST_CNT FROM ( SELECT CO.COUNTRYID , CI.CITYID , COUNT(R.RESTAURANTID) AS REST_CNT FROM RESTAURANT R JOIN COUNTRY CO ON R.COUNTRYID=CO.COUNTRYID JOIN CITY CI ON R.CITYID=CI.CITYID WHERE R.ISACTIVE=1 GROUP BY CO.COUNTRYID , CI.CITYID ) P2 WHERE P2.REST_CNT >= P1.REST_CNT AND P2.COUNTRYID = P1.COUNTRYID ) AS P3 WHERE P3.REST_CNT
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following