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 ?
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
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;
Hi
Hope this helps 
Group by
Does distinct and order by
If you don't want distinct
Then something else needs to be done ..