EXPERT RESPONSE
Yes, it's tricky. You have to use a correlated subselect.
SELECT Sid, Sname, Rating, City
FROM shoptable X
WHERE Rating =
(SELECT MAX(Rating)
FROM shoptable
WHERE City=X.City)
The inner query gets the maximum rating in the same city as the row that
the outer query is looking at, and the outer query accepts that row into the final result set if its rating has the same value as its city's maximum rating.
You sort of have to look at it for a while to see why it works. The WHERE clause in the inner query selects all the rows in the table that have the same city as the row that the outer query is looking at, which looks at every row in the table one at a time. It's called a correlated subselect, and X is the correlation variable or alias, because the rows looked at in the inner query are related to each row of the outer query by virtue of the cities having to be the same. You can think of it as evaluating the inner query once for each row of the outer query, although in practice the database might not execute it exactly that way (SQL is all about asking for what you want, not how to get it.)
|
