Post reply

Distinct question, waiting on line, thanks

  • March 23, 2007 at 8:41 am

    #61114

    Hi Folks,

    I have table: field1, f2, f3, f4, ... , f10

    f8-f10 are text field

    some of the records in the table are duplicated so I need to select based on the f1 to f7

    How do I write my select query?

    Thanks.

  • March 23, 2007 at 11:27 am

    #696678

    here's an example to show all records based on the criteria you described

    SELECT SOMETABLE.* FROM SOMETABLE

    INNER JOIN

    (

    SELECT F1,F2,F3,F4,F5,F6,F7 FROM SOMETABLE

    GROUP BY F1,F2,F3,F4,F5,F6,F7

    HAVING COUNT(F1) > 1 --FINDS ALL RECORDS WITH DUPLICATES.

    ) X --ALIAS FOR THE SUB SELECT IN PARENTHESIS

    ON  SOMETABLE.F1=X.F1

    AND SOMETABLE.F2=X.F2

    AND SOMETABLE.F3=X.F3

    AND SOMETABLE.F4=X.F4

    AND SOMETABLE.F5=X.F5

    AND SOMETABLE.F6=X.F6

    AND SOMETABLE.F7=X.F7 --NEED TO JOIN ON ALL SAME CONDITIONS

    --ORDER SO THEY ARE TOGETHER

    ORDER BY SOMETABLE.F1,SOMETABLE.F2,SOMETABLE.F3,SOMETABLE.F4,SOMETABLE.F5,SOMETABLE.F6,SOMETABLE.F7

    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!

Viewing 2 posts - 1 through 2 (of 2 total)

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