Solved

Dedupe SQL

Posted on 2004-10-27
324 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
Question by:itimes
    9 Comments
     
    LVL 23

    Accepted Solution

    by:
    Maybe Try this..

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

    where ref2 is nText
    0
     

    Author Comment

    by:itimes
    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
    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
     

    Author Comment

    by:itimes
    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:adilkhan
    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:adilkhan
    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
    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:adilkhan
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now