How to get the maximum value for a row

How to get the maximum value for a row

Can you PLEASE PLEASE PLEASE tell me how to get the maximum value for a row?
e.g.:

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.


      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?

I tried:

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.)


This was first published in April 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top