15:06

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Aggregate function to aggregate function page 2

Sure, you'll meet solving such exercises based on sorting with limit on the number of rows result set. However, such solutions are not legitimate from the standpoint of the standard language and, consequently, have different syntax in different implementations. As an example, I will give the solution of our exercise in the syntax of A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server and MySQL.

SQL Server

Console

Execute

SELECT TOP 1 AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model 
GROUP BY maker
ORDER BY avg_price DESC;

MySQL

SELECT AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model
GROUP BY maker
ORDER BY avg_price DESC
LIMIT 1;

Both of these solutions take only the first row of the sorted descending a set of average prices.

Do beginners learn SQL is often the problem the definition of maker, for whom is achieved the desired maximum / minimum. In other words, you want to find the maximum average price and the maker, the average price of a PC which is equal to the maximum average price.

Unconventional means to solve this exercise is actually discussed above query:

Console

Execute

SELECT TOP 1 maker, AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model 
GROUP BY maker
ORDER BY avg_price DESC;

Using the maker in the column list of SELECT clause is quite acceptable, because by that column runs grouping. However, there is a "trap". It stems from the fact that the maximum can be achieved for several makers and in the formulation of the exercise they need to show everyone, while we limit the sample only one (first) string. In this case dialect T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL has additional argument WITH TIES. The logically correct solution will look like:

Console

Execute

SELECT TOP 1 WITH TIES maker, AVG(price) avg_price
FROM Product P JOIN PC ON P.model = PC.model 
GROUP BY maker
ORDER BY avg_price DESC;

However, for portability of code the standardized solution is to be preferred.

Notes:

Implementation would require a minimum of efforts, if the test solution used for testing, would work on all alleged DBMS without changing its code. Therefore, compliance with standard may be one of requirements specification for the project.

Pages

MySQL MAX aggregate functions TOP N WITH TIES LIMIT

Previous [The sorting and NULL values]

[Product of column values] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

SELECT statement

Getting summarizing values

Aggregate function to aggregate function page 2