Can you PLEASE PLEASE PLEASE tell me how to get the maximum value for a row?
SHOPTABLE Sid Sname Rating City S1 JOE 3 SYDNEY S2 MARCO 5 MELBOURNE S3 LI 4 SYDNEY S4 ANGELO 2 BRISBANE S5 DI 1 PERTH S6 PRU 5 MELBOURNE S7 VLAD 5 SYDNEY S8 DREW 4 HOBART S9 KIM 3 CAIRNS
How can I display the records that have THE HIGHEST RATING FOR THEIR CITIES?
SELECT * FROM ShopTable WHERE ShopTable.Rating IN (SELECT max(ShopTable.Rating) FROM ShopTable)
But then all I get are those records with Rating being 5. THIS IS SO FRUSTRATING...!!!!!! Can you please help me? I will be so appreciative.
P.S. I AM USING ACCESS 2000
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.)