CODEWITHSUNDEEP
sqlsql-server
Evohc
Evohc

Reputation: 35

SQL query multiple columns in SELECT - one needs to be DISTINCT

This is my SQL code:

SELECT 
    T.Template_Id, T.TemplateName,
    CONVERT(NVARCHAR(MAX), T.CatalogDescription),
    T.MasterNo, T.Customer_Id,
    O.Quantity, O.Cost 
FROM 
    Template as T 
INNER JOIN 
    [Order] AS O ON T.Template_Id = O.Template_Id 
ORDER BY 
    O.Cost

My problem is that none of the fields I'm selecting are unique, and I want to have T.Template_Id be DISTINCT, which I couldn't find a way to do. Other columns don't matter, as long as they're there and that the T.Template_Id column is DISTINCT (no duplicates).

Upvotes: 1

Views: 52

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415755

Other fields don't matter.

If this is really true*, you can do it like this:

SELECT T.Template_Id, MAX(T.TemplateName) As TemplateName,
    CONVERT(NVARCHAR(MAX),MAX(T.CatalogDescription)) As CatalogDescription,
    MAX(T.MasterNo) As MasterNo, MAX(T.Customer_Id) As CustomerId,
    MAX(O.Quantity) As Quantity, MAX(O.Cost) As Cost
FROM Template as T 
INNER JOIN [Order] as O ON T.Template_Id=O.Template_Id 
GROUP BY T.Template_Id
ORDER BY MAX(O.Cost)

It's a bit less unusual to see queries where it doesn't matter which corresponding Order fields are used, as long as you're using fields from the same Order record. In that case, you can do it like this:

SELECT T.Template_Id, T.TemplateName, 
     CONVERT(NVARCHAR(MAX),T.CatalogDescription), 
     T.MasterNo, T.Customer_Id, O.Quantity, O.Cost
FROM Template as T 
CROSS APPLY (SELECT TOP 1 * FROM [Order] WHERE T.Template_Id=[Order].Template_Id) As O
ORDER BY O.Cost

Assuming, of course, that the records at least within the Template table are already unique based on the ID. This has the nice benefit of also making it easier to select which order is chosen, simply by adding an ORDER BY clause inside the nested query.

* Tip: It turns out this is rarely the case. You'll pretty much always find out that it does matter at some point, for at least one of the fields.

Upvotes: 1

Joe C
Joe C

Reputation: 3993

SQL will not allow you to aggregate on only specific fields in a dataset, they must all be aggregated. If it did then like VFP and other database engines that allow this, will just pick a row to fill in the other values.

If you are trying to achieve what I believe you are, then you want a list of all distinct values for the one field and just a sample of the other fields.

I have done this before using window functions such as Rank and Row_Number depending on exactly what I was trying to accomplish. This allows you to also choose your samples if you want such as ordering by OrderDate Desc to get sample fields from the most recent order for a customer.

Upvotes: 0

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

Typically you'd GROUP BY that column, but that requires specifying an aggregate function for all the other columns. In your case that may work since you say the other columns don't matter (which makes me wonder why they're being returned).

SELECT T.Template_Id, Max(T.TemplateName), 
Max(CONVERT(NVARCHAR(MAX),T.CatalogDescription)), Max(T.MasterNo), Max(T.Customer_Id), 
Max(O.Quantity), Max(O.Cost) 
FROM Template as T INNER JOIN [Order] as O ON T.Template_Id=O.Template_Id 
GROUP BY T.Template_Id
ORDER BY O.Cost

Upvotes: 1

Related Questions