Any other variation of this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Any other variation of this query?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-08 : 07:03:06

I have a table like this:

TblContact
RowId Name Customer
1 Karunakaran ABC
2 Karunakaran DEF
3 Vijay XYZ
4 Anil ABC

I need the query to give me an output where the same name is there for different customer,like this:
RowId Name Customer
1 Karunakaran ABC
2 Karunakaran DEF

I get this output with any of the following 2 queries.

Select distinct TblA.Name,TblA.RowId,TblA.Customer from TempTable as TblA ,TempTable as TblB where TblA.endusercontactname = TblB.endusercontactname and TblA.Endusercustname <> TblB.Endusercustname

Select distinct TblA.Name,TblA.RowId,TblA.Customer from Temptable as TblA, (Select distinct Name, Customer from TempTable) as TblB where TblA.Name = TblB.Name and TblA.Customer <> Tblb.Customer

On the execution plan both looks similar. I need to perfom this on a table with around a million records. Is there any other better way to do, so that the performance can be improved.

Karunakaran

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 08:18:25

try this:
select t1.* from TempTable t1 join (select name from TempTable group by name having count(*)>1) t2 on t1.Name = t.Name

Go with the flow & have fun! Else fight the flow

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-08 : 10:13:42

I think I havent posted clearly of the requirement. All I want is the unique rowid for the name where it has different customers.

Karunakaran

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-08 : 11:05:15

>>Is there any other better way to do, so that the performance can be improved.

Tough to answer without the DDL of the table *including* all indexes on it.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-09 : 02:16:40

quote:
Originally posted by PW

>>Is there any other better way to do, so that the performance can be improved.

Tough to answer without the DDL of the table *including* all indexes on it.

This table structure is just a part of the staging temp table I use to import data. No Indexes here.Rowid is a Identity column Not Null. other columns will allow nulls.

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-12 : 11:16:36

One More Query:


SELECT T1.Rowid,T1.ResellerCustomerName,T1.ResellerAccountNo,T1.EndUserCustNo,T1.EndUserCustName,T1.EndUserContactName,T1.EndUserContactEmail,
T1.OrderPONo,T1.OrderPODate,T1.OrderNumber,T1.OrderDate,T1.SVCNumber,T1.SVCDescription,T1.OrderQuantity,T3.ContactId
FROM TempView T1,Reseller T2,CustomerDetail T3 WHERE T1.RevenueType = 'Maintenance' and T1.ResellerAccountNo = T2.ResellerAccountNo 
and T2.ResellerAccountNo = T3.ResellerAccountNumber and T1.EndUserContactName = T3.ContactFirstName order by rowid

other better way to write this query?
Due to client confidential issues I'm not in a position to post sample data here..

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources