Post reply

Script to get distinct data

  • February 21, 2007 at 8:39 am

    #116284

    Hello,

    I have like 75 rows in a table  and we have some duplicates like this out of 75 is it possible to get just one row

    from the below data.

     

                             ENO Fname Lname Dept Start End Chngdate
    1 Sam Jonson Dept1 3/14/2005 3/31/2007 9/26/2006
    1 Sam Jonson Dept2 3/14/2005 3/31/2007 9/26/2006
    1 Sam Jonson Dept3 3/14/2005 3/31/2007 9/26/2006
    1 Sam Jonson Dept4 3/14/2005 3/31/2007 9/26/2006

     

     

    Any help on this is greatly appreciated.

     

    Thanks,

  • February 21, 2007 at 8:54 am

    #690940

    there's several ways...I would suggest a group by:

    SELECT ENO, Fname, Lname, MIN(Dept) as Dept, MIN(Start) as Start, MIN(End) as [End], MIN(Chngdate) as Chngdate FROM sometable

    group by ENO, Fname, Lname

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 21, 2007 at 11:14 am

    #690984

    Lowell,

    Thanks for your Speedy response!

    There are other columns also in that table also you want me to just add the columns in the select list and the group by list right.

     

    Thanks,

     

     

  • February 21, 2007 at 11:50 am

    #690995

    i kind of assumed that the uniqueness you wanted was firstname/lastname, so all other columns would be a min() or max(), unless those columns make the row the distinct -ness you are looking for; if they are, you'd add them to the group by section.

    HTH.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 22, 2007 at 8:26 am

    #691177

    Using a simple GROUP BY and MIN may give result records where each aggregate field returns a value from a different source record.  This may not be acceptable.

    If the records have a single-field primary key (INT IDENTITY for instance), you can get unique records with:

    SELECT * FROM table t

    INNER JOIN (

        SELECT MIN(pk) AS pk FROM table

        GROUP BY f1, f2, ...

    ) k ON t.pk = k.pk

    I have had cases where there was a complex set of factors that determined the "best" record from each set of duplicates.  This can be handled using a temp table and a unique index:

    CREATE TABLE #t (

       ...)

    CREATE UNIQUE INDEX ix ON #t (f1, f2, ...) WITH IGNORE_DUP_KEY

    INSERT INTO #t

    SELECT ... FROM table

    ORDER BY CASE WHEN condition1 THEN weight1 ELSE 0 END

        + CASE WHEN condition2 THEN weight2 ELSE 0 END

        + CASE WHEN condition3 THEN weight3 ELSE 0 END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply