Timallard Mar 23, 2005   3:48 PM GMT

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:

SloopJohnB Mar 23, 2005   3:50 PM GMT

Have you tried SELECT TOP 3 ... HTH, John Barone

0 pointsBadges:

Rolaaus Mar 23, 2005   3:57 PM GMT

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:

Stevewaltz Mar 23, 2005   7:49 PM GMT

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:

catalin67 Mar 24, 2005   11:00 AM GMT

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:

r937 Mar 24, 2005   11:26 AM GMT

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:

Julius Mar 24, 2005   12:39 AM GMT

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:

Ronjar Mar 28, 2005   8:17 AM GMT

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:

r937 Mar 28, 2005   8:53 AM GMT

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:

MFleming Mar 30, 2005   12:37 AM GMT

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: