CODEWITHSUNDEEP
sqlms-accessms-access-2010
user2430036
user2430036

Reputation: 45

Pick One Row per Unique ID from duplicate records

In Ms.Access 2010, I have a similar query table like one below where its displaying duplicate records. Problem is that even though I have unique ID's, one of the field has different data than other row since I have combined two seperate tables in this query. I just want to display one row per ID and eliminate other rows. It doesn't matter which row I pick. See below: 

ID - NAME - FAVCOLOR
1242 - John - Blue
1242 - John - Red  
1378 - Mary - Green

I want to just pick any of the the row with same ID. It doesn't matter which row I pick as long as I am displaying one row per ID is what matters.

ID - NAME - FAVCOLOR
1242 - John - Red  
1378 - Mary - Green

Upvotes: 2

Views: 2056

Answers (3)

HansUp
HansUp

Reputation: 97101

Use the SQL from your current query as a subquery and then GROUP BY ID and NAME. You can retrieve the minimum FAVCOLOR since you want only one and don't care which.

SELECT sub.ID, sub.NAME, Min(sub.FAVCOLOR)
FROM
    (
        SELECT ID, [NAME], FAVCOLOR
        FROM TABLE1
        UNION ALL
        SELECT ID, [NAME], FAVCOLOR
        FROM TABLE2
    ) AS sub
GROUP BY sub.ID, sub.NAME;

Note NAME is a reserved word. Bracket that name or prefix it with the table name or alias to avoid confusing the db engine.

Upvotes: 2

Andrew
Andrew

Reputation: 8703

If you just want the IDs, why is the color in the query? Maybe I'm missing something. The only thing I could suggest is to use some aggregate function (min, max) to get one color.

    Select
    id,
    name,
    max(favcolor)
    from (
    (select * from table1) t1
    union (select * from table2) t2 )t
    group by
    id,
    name

Upvotes: 0

Jay
Jay

Reputation: 152

Try selecting union without the ALL parameter and see if you get the desired result.

Your new query would look like

"SELECT ID, NAME, FAVCOLOR FROM TABLE1; UNION SELECT ID, NAME, FAVCOLOR FROM TABLE2;"

Upvotes: 0

Related Questions