Find duplicate rows, SQL Server

irlrobins

I have a table with the following design

TransactionNum int
Membernum int
Transactiondate smalldatetime
Transactionamt money
Currency nvarchar
TransactionType nvarchar
TransactionMethod nvarchar
Transactionref ntext

I'm looking to find all duplicate rows using this:
[PHP]SELECT MemberNum, TransactionDate, TransactionAmt, Currency, TransactionType, TransactionMethod, TransactionRef, COUNT(MemberNum) AS Expr1,
COUNT(TransactionDate) AS Expr2, COUNT(TransactionAmt) AS Expr3, COUNT(Currency) AS Expr4, COUNT(TransactionType) AS Expr5,
COUNT(TransactionMethod) AS Expr6, COUNT(TransactionRef) AS Expr7
FROM TblPayment
GROUP BY MemberNum, TransactionDate, TransactionAmt, Currency, TransactionType, TransactionMethod, TransactionRef
HAVING (COUNT(MemberNum) > 1) AND (COUNT(TransactionDate) > 1) AND (COUNT(TransactionAmt) > 1) AND (COUNT(Currency) > 1) AND
(COUNT(TransactionType) > 1) AND (COUNT(TransactionMethod) > 1) AND (COUNT(TransactionRef) > 1)[/PHP]

(TransactionNum is unique so I don't compare it)

But when I run this I get
"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

I assume this is against the TransactionRef column but how do I get around this?

Ginger

Change your equals operator to like and leave out the % to have it do an expensive exact match!

irlrobins

Ginger wrote: »

Change your equals operator to like and leave out the % to have it do an expensive exact match!

What equals operator?

Ginger

Sorry I misread your query

oeb

GROUP BY is a sorting feature, thus, it can not be used with nText.

Solutions include converting it to a VARCHAR instead either in your schema or using the cast function

CAST(Transactionref as varchar(500))

EDIT:
Or just don't group by that field

kayos

The problem is you can not group on ntext.

How long is the data in TransactionRef? If its over the limit for (n)varchar then I would be wondering what sort of reference it is.

If the TransactionRef holds something below the datatype limits for (n)varchar you could just try converting it for the query in question.

Also your query could be a lot neater.... you dont need to count every field! Once you have the rows grouped on every field you just need to check the count on 1 field!

SELECT
	MemberNum, 
	TransactionDate, 
	TransactionAmt, 
	Currency, 
	TransactionType, 
	TransactionMethod, 
	convert(nvarchar,TransactionRef), 
	COUNT(MemberNum) AS Rows
FROM
	TblPayment 
GROUP BY 
	MemberNum, 
	TransactionDate, 
	TransactionAmt, 
	Currency, 
	TransactionType, 
	TransactionMethod, 
	convert(nvarchar,TransactionRef)
HAVING      
	COUNT(MemberNum) > 1

irlrobins

Thanks kayos that seems to have worked