Select and ID Column

Question

Select and ID Column

Aaron Watson

SSC Journeyman

Points: 76
More actions
February 6, 2006 at 6:21 am

#93252

I had posted a question last week about about a getting rid of duplicates in a select statement that was answered thanks to people on this forum. After working on it I ran into a problem this weekend.
I have the following SQL statment that returns the correct number of rows

select
Distinct CustomerName,CustomerNumber,SalesmanName,Min(ItemDescription) AS ItemDescription,OrigionalDateofService,InsuranceCarrier, Diag1,Diag2,Diag3,Diag4 from O2
Group By
CustomerName,
CustomerNumber,
SalesmanName,
OrigionalDateofService,
InsuranceCarrier,
Diag1,
Diag2,
Diag3,
Diag4
The problem that I run into is that my O2 table also has and ID column, an auto Identity field, that must be included within the result set for development reasons.
When I add the ID to the select statement it returns all the rows of the column. What would be the correct way to include the ID field and get the right set of data.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

Well one thing is you'd have to determine what "id" you want to return. Do you just return one of them for each distinct set you have or ..do you need to return each one ?

Mathew J Kulangara
sqladventures.blogspot.com

Aaron Watson SSC Journeyman Points: 76 More actions · Answer 2

I only need to return the one for the distinct combination.

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 3

If I'm understanding you...

select Distinct [ID],CustomerName,CustomerNumber,SalesmanName,Min(ItemDescription) AS ItemDescription,OrigionalDateofService,InsuranceCarrier, Diag1,Diag2,Diag3,Diag4 from O2

Group By

[ID],

CustomerName,

CustomerNumber,

SalesmanName,

OrigionalDateofService,

InsuranceCarrier,

Diag1,

Diag2,

Diag3,

Diag4

By the way if {id] is trully an identity column ..you shouldn't need distinct at all.

HTH

Mathew J Kulangara
sqladventures.blogspot.com

aureolin Hall of Fame Points: 3520 More actions · Answer 4

Try the following:

select Distinct CustomerName,CustomerNumber,SalesmanName,Min(ItemDescription) AS ItemDescription,OrigionalDateofService,InsuranceCarrier, Diag1,Diag2,Diag3,Diag4,

min(ID) as ID

from O2