CODEWITHSUNDEEP
sql
Dimitra Micha
Dimitra Micha

Reputation: 2129

Distinct Select in SQL and Order by

I wrote a query in sql, where I will keep just one time the Companies.ID, that is why I use Distinct.

SELECT DISTINCT Companies.ID, Companies.Company, Abo.Ende 
FROM Companies
LEFT JOIN Abo on Companies.ID = Abo.CompanyID 
ORDER BY Abo.Ende

The table Abo.Ende has more entries for the Abo.CompanyID and I would like to get as result the distinct Companies.ID with the most recent Abo.Ende. Note: Abo.Ende is a datetime field and I would like to get the newest.

I am wondering if this is the best way to do that. Is there another way?

Upvotes: 1

Views: 159

Answers (3)

user330315
user330315

Reputation:

You need group by, not distinct:

SELECT Companies.ID, Companies.Company, max(Abo.Ende)
FROM Companies
LEFT JOIN Abo on Companies.ID = Abo.CompanyID 
GROUP BY Companies.ID, Companies.Company

A comment on your DISTINCT usage:

DISTINCT operates on the whole row. So it will return distinct combinations of all column values in a single row. If you include a PK column (Probably companies.id in your case), distinct would not "remove" any rows as the PK is unique by definition and if one column is unique across all rows, then all the columns of the result are "unique" (in the sense of the DISTINCT operator) as well.

Upvotes: 4

James Culshaw
James Culshaw

Reputation: 1057

Try:

SELECT Companies.ID, 
       Companies.Company, 
       MAX(Abo.Ende) AS Ende
FROM Companies
LEFT JOIN Abo on Companies.ID = Abo.CompanyID
GROUP BY Companies.ID, 
       Companies.Company

Upvotes: 1

Aboelseoud
Aboelseoud

Reputation: 555

SELECT Companies.ID, Companies.Company, max(Abo.Ende)
FROM Companies
LEFT JOIN Abo on Companies.ID = Abo.CompanyID 
GROUP BY Companies.ID, Companies.Company

Upvotes: 1

Related Questions