09:30

# 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_priceFROM Product P JOIN PC ON P.model = PC.model GROUP BY makerORDER BY avg_price DESC;`

MySQL
`SELECT AVG(price) avg_priceFROM Product P JOIN PC ON P.model = PC.modelGROUP BY makerORDER BY avg_price DESCLIMIT 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_priceFROM Product P JOIN PC ON P.model = PC.model GROUP BY makerORDER 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_priceFROM Product P JOIN PC ON P.model = PC.model GROUP BY makerORDER 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 1 2 3