Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    15

    Question Unanswered: How to sort duplicated records first ?

    Dear All,

    I have a small question which I gave up after a week trying to solve.

    I have a table having around 15,000 records, about 10% of the records have duplicates in 1 text field.

    For some daily tasks, we often have to sort the records in the following order:

    -Dupplicated records. If there is 2 or more same records, then the records are sorted by ID
    -Non-duplicated records, sorted by ID.

    I Have tried with union query, but without success.

    Thanks alot for any help.

    Trung

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Order By yourField, Id

  3. #3
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    You may misunderstand me.

    The records supposed to sort: The duplicated records first and then the non-duplicated records.

    What you show me is to sort the first by the MyField, and then by the ID. This is nothing to do with duplicated records!

    Trung

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    PHP Code:
    SELECT DISTINCTROW  yourTableName.* INTO anyOtherTableName FROM    
       yourTableName
    WHERE 
    ((( yourTableName.1stOrderByIn (SELECT 1stOrderBy FROM         
            yourTableName 
    As Tmp GROUP BY [1stOrderByHAVING Count(*)>))) 
    ORDER BY yourTableName.1stOrderBy 
    Where yourTableName is your Table
    1stOrderBy Field (or Fields separated by comma) to order by

    Hope it can help
    This will collect all the duplicates in the Tmp Table

  5. #5
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    According to your code, all the duplicated records will be collected to the new tmp table.

    For my task, after collecting all dupplicated records into a new tmp table, I have to add into this tmp table non-duplicated records ?

    After that, the new tmp table will have all the records of the original table, sorted first by dupplicated records, then by the ID field ?

    The operation seem to me take time as there is append and create table, as well as using IN and NOT IN operators.

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You can do it with 2 queries. Create a query called 'qryListCount' and paste this code into it, puttting the name of your file in place of 'List'.

    SELECT List.ID, IIf(Count(List.ID)=1,1,2) AS Dups
    FROM List
    GROUP BY List.ID;

    And then create another query with this code:

    SELECT List.ID
    FROM qryListCount INNER JOIN List ON qryListCount.ID = List.ID
    ORDER BY qryListCount.Dups DESC , List.ID;

    TD

  7. #7
    Join Date
    Dec 2003
    Posts
    15
    Thank for your tip

    Your solution does not work under my situation.

    1. The dupplicated field is not the ID field. It is another text field. When I try putting ID field in the query with GROUP BY, the count does not work:
    SELECT List.ID, List.DupField, IIf(Count(List.DupField)=1,1,2) AS Dups
    FROM List GROUP BY List.ID, List.DupField;

    2. The 2-nd query does not work if I try to use the 1-st query with duplicated field, because I can not join fields with dupplicated records.

    Trung

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    I missed the part in your first post that the duplicated data was in another text field. Here is a solution that should work. Create a query called 'qryListCount' and paste this code into it, puttting the name of your file in place of 'List'.

    SELECT List.DupText, Min(List.ID) AS MinOfID, IIf(Count([DupText])>1,2,1) AS Dups
    FROM List
    GROUP BY List.DupText;

    And then create another query with this code:

    SELECT List.ID, List.DupText
    FROM qryListCount INNER JOIN List ON qryListCount.DupText = List.DupText
    ORDER BY qryListCount.Dups DESC , qryListCount.MinOfID, List.ID;

    TD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •