how to write querey return the unique CustomerId with the first CustomerName ?

AJ Hoge

hii , i have question and i wont ask this question in form of the example. if we have table named CUSTOMER: CUSTOMER TABLE: CustomerID : Integer (Not a primary Key) CustomerName: TEXT

we note that CustomerId number 25 has deffrint spiling of the CustomerName for the same Customer! i need to return the unique CustomerId with the first CustomerName. i write this querey :

select DISTINCT CustomerID , CustomerName
From Customer

but it got me the four records !! the 25 is dublicated twies becuse the spiling of the name are deffrint for the same Customer how to write querey return the unique CustomerId with the first CustomerName? Many Thanks

il_masacratore

Maybe it ins't the best solution but you can use a group by (CustomerId), then you have to choose wich CustomerName is the choosen (min(CustomerName), max(CustomerName...). Sql: >select CustomerId, max(CustomerName) from Customer group by CustomerId.

Visit my blog at http://dotnetforeveryone.blogspot.com

J4amieC

SELECT
@customerId as customerId,
(SELECT TOP 1 customerName FROM Customer WHERE customerId=@customerID ORDER BY customerName) AS customerName

Whenever your data looks this bad, its a sure-fire signal to clean your data, and ensure your apps only insert clean data.

AJ Hoge

:thumbsup:Ohhh...Yes it is work !! many thanks

Niladri_Biswas

You can even try this declare @tblcustomer table(customerid int,customername varchar(50)) insert into @tblcustomer select 12,'Jone' union all select 25,'Mark' union all select 25,'Marc' union all select 10,'AJ'

select x.customerid
,x.customername
from
(
select
ROW_NUMBER() over (partition by customerid order by customerid) as rn
,customerid
,customername
from @tblcustomer)X(rn,customerid,customername)
where rn = 1

Output:

customerid customername
10 AJ
12 Jone
25 Mark

:)

Niladri Biswas

AJ Hoge

hii ,sorry what do you mean by sure-fire signal ? :^) yes i agree with you that the data is look bad , but i work on a huge data that which is extracted by the Customers them self so i can not edit any thing. :( many thanks

i j russell

How do you know which is the first CustomerName? Is the 'first' name always the correct one?

J4amieC

Member 3018046 wrote:

hii ,sorry what do you mean by sure-fire signal

It means that it is time to examine why you have 2 customer records with the same Id, but different names.