Groupping instead unique

Transact-SQL
#1

Hello, everyone

I try to

SELECT third.City,
(SELECT max(first.ReorderLevel) FROM dbo.Products as first WHERE first.SupplierID = second.SupplierID)
FROM dbo.Products AS second
INNER JOIN dbo.Suppliers AS third ON second.SupplierID = third.SupplierID

From nothwind

And I would like to have output only unique values without distinct, but using group by,
Could you show how its possible ?

#2

Hi Ant

Is there a reason why you don't want to use the DISTINCT property?

The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Just adding GROUP BY probably won't have the required effect.

SELECT third.City,
(SELECT max(first.ReorderLevel) FROM dbo.Products as first WHERE first.SupplierID = second.SupplierID)
FROM dbo.Products AS second
INNER JOIN dbo.Suppliers AS third ON second.SupplierID = third.SupplierID
GROUP BY third.city
#3

Why are you using a sub-query to get the max...

SELECT s.City, MaxReorder = max(p.ReorderLevel)
FROM dbo.Products p
INNER JOIN dbo.Suppliers s ON s.SupplierID = t.SupplierID
GROUP BY s.City;
#4

Hi

Hope this helps :slightly_smiling_face::slightly_smiling_face:

Group by
Does distinct and order by

If you don't want distinct
Then something else needs to be done ..