USE OF DISTINCT with MAX Aggregate

Question

Why the command Sql's output mention error message for this quiery statement:

SELECT Distinct SupplierId, max(Price)
FROM Products;

I Tried to get mention of the SupplierId who has the highest price in a double column one row entries. Any suggestions or examples clarifying that issue please ? Many thanks.

Answer 1

The issue is that max is an aggregation, that is, a scalar value for a group, whereas the id is an ungrouped item. You can achieve what you want via:

SELECT SupplierId, max(Price)
FROM Products
GROUP BY SupplierId;

this is how you make SupplierId an aggregate column in your query, basically specifying that you will have groups from records whose SupplierId is unique for each group and having as many groups as many distinct supplier ids you have and this way it's clear what SupplierId is for each of your group and you aggregate price by computing its max as well.

Imagine a case when you have 1000 records with different supplier ids. If you would have to infer that value without grouping, then you would not be able to do so, because there could be many such values. But, if you make groups of supplier ids like my suggestion, then the supplier id of each group will be clear.