Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dedupe SQL

Posted on 2004-10-27
9
Medium Priority
?
397 Views
Last Modified: 2008-01-09
Hi

Im trying to dedupe my SQL 7 DB from my SQl statemnt

Im currently using teh following :

var SQL = "SELECT DISTINCT ref1 FROM tbl1

but beacse some of my fields are NTEXT I cannot request all the filds i wish to display ??

Is this the nest way to dedupe with SQL ??

Thanks
IT



0
Comment
Question by:itimes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 23

Accepted Solution

by:
Saqib Khan earned 200 total points
ID: 12423409
Maybe Try this..

var SQL = "SELECT DISTINCT ref1, Convert(nvarchar(10000), ref2) FROM tbl1

where ref2 is nText
0
 

Author Comment

by:itimes
ID: 12423545
adilkhan,

Ok teh covert seems to work after i reduced teh size down to 4000 as it says this is teh max but i now get

"ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal. "

What is the RS name for ref2 now ???
0
 

Author Comment

by:itimes
ID: 12423569
Sorry just added the " as ref2 " line .... works now ....

so its still doing teh dedupe but converting the NTEXT into a compatable N#nvarchar ... is teh "convert" just part of SQL then ???
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:itimes
ID: 12423657
adilkhan ,

I spke too soon there are no errors but it seems to ignore the DISTINCT clause as im stil getting duplicated records with teh same ref1 values ...... ??

Help !!!
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12424699
reason why you getting duplicates is because maybe second column does not have 100% Same data for all the other distinct Rows.

1 hello
1 hello.

Not Distinct.

0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12424753
Distinct keyword will work fine As long as the ROWS specified int he Select Statement are matching, if 1 ROW has different values for 1 Column then other and appears twice then its not Distinct.

Converts change the DataType Dynamicly on the Fly for SQL statement. yes its T-SQL keyword.
0
 

Author Comment

by:itimes
ID: 12431310
adilkhan,

Your right the "second column does not have 100% Same data" so how cani dedupe on teh first coleum but still diaplsy all teh recordset info for each record found ???

Sorry about the dealy in getting back to you ...

Thanks

IT
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 12435120
if you think about it, its not possible. You can do a Group by and Show the Count of Other Columns.


var SQL = "SELECT ref1, Count(Convert(nvarchar(10000), ref2)) FROM tbl1 Group By ref1"
0
 

Author Comment

by:itimes
ID: 12442180
adilkhan,

Thansk for teh feedback...

Im looking at droping teh recordset into an arrya and then deduping that array, that way iv got more control of what i see aftre teh dedupe and its still fast.

Thanks

IT
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question