SQL Server Performance Forum – Threads Archive

Problem Facing nText Data Type

Hi, I am facing a problem with the duplicate data exist in one of the legacy data base.. The issue is; one of the tables has few nText, ID [int], SubDate [date] fields. So some of the SubDate fields are duplicated. I need the entire data with a DISTINCT of date field, but we can not apply the Distinct clause when a table having nText data type. I tried to get the data using some GROUP BY, all are failing [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] So can any one suggest some solution for resolve the issue? Thanks Ajith

You could try adding another identity column and then try replicating data and delete with that new ID. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Even though we add a new identity column, we canot group by the table value, since it is using the nText type . Can you please guide me what the speps i need to do for that ? Thnaks
Ajith

Use a correlated subquery to find the first ID value for the same SubDate:
SELECT t1.ID, t1.SubDate, t1.nText
FROM MyTable t1
WHERE t1.ID = (SELECT MIN(t2.ID) FROM MyTable t2 WHERE t2.SubDate = t1.SubDate)

Thanks for your Reply, its fine

]]>