﻿ SQL: Highest grade for each of the cities of the customers

SQL Exercises: Highest grade for each of the cities of the customers

SQL Aggregate Functions: Exercise-8 with Solution

From the following table, write a SQL query to find the highest grade of the customers in each city. Return city, maximum grade.

Sample table: customer

Sample Solution:

``````SELECT city,MAX(grade)
FROM customer
GROUP BY city;
``````

Output of the Query:

```city		max
London		300
Paris		300
New York	200
California	200
Berlin		100
Moscow		200
```

Explanation Code

The said SQL statement retrieves the maximum "grade" value for each "city" in the 'customer' table. A "GROUP BY" clause is used in the query to group the 'customer' table according to a city, and a "MAX" function is used to determine the maximum grade for each group based on the "GROUP BY" clause.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation :

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find the minimum purchase amount of all the orders.
Next SQL Exercise: Highest purchase amount ordered by the each customer.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook