Q
Manage Learn to apply best practices and optimize your operations.

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

      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 last published in April 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close