Select and ID Column

6 Feb 2006 1:31 PM

AwAnvil

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.

6 Feb 2006 2:01 PM

Andrew J. Kelly

That makes no sense. As a matter of fact the current query makes no sense
since you are using a DISTINCT along with a GROUP BY. What good is the ID
when you are grouping? Maybe if you explain what it is you are trying to
do we can suggest a proper query. Providing DDL will help as well.

--
Andrew J. Kelly SQL MVP

Show quote

"AwAnvil" <aswatso***@gmail.com> wrote in message
news:1139232719.089650.125620@o13g2000cwo.googlegroups.com...
>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.
>

6 Feb 2006 2:18 PM

AwAnvil

In my O2 table, I am trying to select a series of orders for a web
application, the result set will be displayed in a datagrid. The user
is wanting to see the information based on the CustomerName and Item
Description. The information would be Customer A has Items A,B,C and
say Customer B has Items B.

The user only wants to see one listing per Customer, ie. I only need to
see where in example above the use would see Customer A Item A and then
Customer B Item B. The user want to control what is shown by the item
name.

6 Feb 2006 11:32 PM

Erland Sommarskog

AwAnvil (aswatso***@gmail.com) writes:
Show quote

> 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.

I echo Andrew and say that id does not really make any sense, and your
question makes me believe you have not really understood the business
problem.

Nevertheless, instead of adding "ID" had MIN(ID) or MAX(ID). And remove
the DISTINCT.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx