Right way to use distinct in SQL Server

Question

I am trying to retrieve some records based on the query

Select distinct 
    tblAssessmentEcosystemCredit.AssessmentEcosystemCreditID,
    tblSpecies.CommonName 
from 
    tblAssessmentEcosystemCredit
left join 
    tblSpeciesVegTypeLink on tblAssessmentEcosystemCredit.VegTypeID = tblSpeciesVegTypeLink.VegTypeID 
left join 
    tblSpecies on tblSpecies.SpeciesID = tblSpeciesVegTypeLink.SpeciesID
where 
    tblAssessmentEcosystemCredit.SpeciesTGValue < 1

The above query returns 17,000 records but when I remove tblSpecies.CommonName, it retrieves only 4200 (that's actually correct).

I have no idea how to distinct only tblAssessmentEcosystemCredit.AssessmentEcosystemCreditID column and retrieve all other table columns in the query.

See this topic: http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns — JonasB, Oct 24 '14 at 00:39
@JonasB, i tired what they advised but i got an exception "Column 'tblSpecies.CommonName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." — Usher, Oct 24 '14 at 00:41
This query selects the different COMBINATION of AssessmentEcosystemCreditID and CommonName; if you want only one row per value of AssessmentEcosystemCreditID then you need to use a GROUP BY, as suggested by @JonasB; however, in that case, there could be several values of CommonName per value of AssessmentEcosystemCreditID , and so SQL requires you to specify WHICH one you want. — okaram, Oct 24 '14 at 00:47
... and the easiest way to select which one you want is wrap it in an aggregate function like MIN or MAX..... which will pick the alphabetically first or last one. If that's what you want. — Nick.Mc, Oct 24 '14 at 00:50

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

See this topic: mySQL select one column DISTINCT, with corresponding other columns

You probably have to deactivate ONLY_FULL_GROUP_BY, see http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

edited May 23 '17 at 12:06

Community

1
1

answered Oct 24 '14 at 00:48

JonasB

458
3
9

score 1 · Accepted Answer · answered Oct 24 '14 at 00:49

This query selects the different COMBINATION of AssessmentEcosystemCreditID and CommonName; if you want only one row per value of AssessmentEcosystemCreditID then you need to use a GROUP BY, as suggested by @JonasB; however, in that case, there could be several values of CommonName per value of AssessmentEcosystemCreditID , and so SQL requires you to specify WHICH one you want

Select  tblAssessmentEcosystemCredit.AssessmentEcosystemCreditID ,
         max(tblSpecies.CommonName) as CommonName,
         min(tblSpecies.CommonName) as CommonName2, -- so you can verify you only have one value
 from tblAssessmentEcosystemCredit
 left join tblSpeciesVegTypeLink 
      on tblAssessmentEcosystemCredit.VegTypeID = tblSpeciesVegTypeLink.VegTypeID 
 left join tblSpecies on tblSpecies.SpeciesID= tblSpeciesVegTypeLink.SpeciesID
 where tblAssessmentEcosystemCredit.SpeciesTGValue <1
 GROUP BY tblAssessmentEcosystemCredit.AssessmentEcosystemCreditID

Right way to use distinct in SQL Server

2 Answers2