MYSQL: Displaying 3 reviews from 3 cities with apartments w/ most recent reviews

[Psytherium]

Hello,

I have an apartment table (apartment id, apartment name, address, city, state, zip, phone, etc) and a reviews table. The reviews table has the review id, apartment id, review datetime, title, review, etc.

I want to display three reviews from the three cities that have the most recent reviews. City A could have the 9 most recent reviews, City B could have the 10th most recent review, and City C could have the 11th and 12th most recent reviews. I would then want to display the top 3 reviews from City A, top 3 reviews from City B, and the top 3 reviews from City C.

If I would have to take some logic to asp.net, how would I atleast get the top 3 cities?

Thanks!
Jon

[bubbisthedog]

misunderstood... will re-post soon. i'll tell you now that this can be accomplished (i'm pretty sure) just using SQL...

[bubbisthedog]

When you say 'top 3,' do you mean 'most recent,' or is that based on some 'grade' of the review?

[Psytherium]

When you say 'top 3,' do you mean 'most recent,' or is that based on some 'grade' of the review?

Sorry I was not clear: Most recent based on the review date.

But, it is not merely this:

SELECT *
FROM apartments a
JOIN reviews r ON r.aptID = a.aptID
ORDER BY reviewdate DESC
LIMIT 9;

Say there are these reviews which are ordered by reviewdate DESC:


Review 1 City 1
Review 2 City 1
Review 3 City 1
Review 4 City 1
Review 5 City 1
Review 6 City 2
Review 7 City 2
Review 8 City 2
Review 9 City 3
Review 10 City 3
Review 11 City 4
Review 12 City 1
Review 13 City 2
Review 14 City 3
Review 15 City 3

I want to return Review 1 (City 1),2 (City 1),3 (City 1), 6 (City 2), 7 (City 2), 8, (City 2), 9 (City 3), 10 (City 3), 14 (City 4)

So the query needs to find the top 3 cities(cities with the most recently reviewed apartments) and return those 3 cities' 3 most recent reviews.

I'm sorry if it is confusing. Let me know if I need to clarify more.

[bubbisthedog]

No problem at all...

Using SQL, for the results that you're seeking, returned in the manner that you're wanting them returned, you'll need to do multiple UNIONS with your SELECT statement, filtering each subset with 'WHERE city_id = ...'. Of course, that's more than likely not feasible --not to mention back-breaking work for the SQL engine if many cities are involved.

So, back to your ASP solution (along with SQL): What you could do is perform a query on 'apartments' that retrieves only distinct cities (hope there's no duplicate city names! ). Then, for each city fetched, your logic (not sure if you're using C#, VB, etc.) will lead to this query:

SELECT *
FROM apartments a
JOIN reviews r ON r.aptID = a.aptID
WHERE city = [[I]city fetched]
ORDER BY reviewdate DESC
LIMIT 0,3;

That's probably very easy if you know remedial C#, VB, whatever you're using.

[mattyblah]

what version of mysql?

[Psytherium]

what version of mysql?

5.0.27