CODEWITHSUNDEEP
sqlms-accessduplicatesdistinctms-access-2010
Popopinsel
Popopinsel

Reputation: 31

Select only records with distinct values in a certain row

I have following sample data:

ID    Name    Street    Number    Code
100   John    Street1   1         1234
130   Peter   Street1   2         1234
135   Bob     Street2   1         5678
141   Alice   Street5   3         5678
160   Sara    Street1   3         3456

Now I need a Query to return only the last record because its Code is unique.

Upvotes: 1

Views: 1101

Answers (3)

Bhavesh Kachhadiya
Bhavesh Kachhadiya

Reputation: 3972

You can try this using CTE :

WITH tempTbl
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY code ORDER BY code ) AS rownm, *
               FROM     yourtable
             )
    SELECT * FROM tempTbl WHERE code NOT IN (SELECT code FROM tempTbl WHERE   rownm > 1)

Upvotes: 0

Popopinsel
Popopinsel

Reputation: 31

Thanks to HansUp, this is my final query now:

SELECT
    A.*
FROM
    (T_NEEDED AS A
INNER JOIN
    (
    SELECT
        CODE
    FROM
        T_NEEDED
    GROUP BY
        CODE
    HAVING
        Count(*) = 1
    ) AS B
ON
    A.CODE = B.CODE)
LEFT OUTER JOIN
    T_UNNEEDED AS C
ON
    A.ID = C.ID
WHERE
    C.ID Is Null
ORDER BY
    A.NAME,
    A.STREET,
    A.NUMBER

Explanation: I have two tables, one with records with IDs that are needed and one with those unneeded. The unneeded IDs might be in the needed table and if they are I want them to be excluded, hence the LEFT OUTER JOIN. Then comes the second part for which opened the question. I want to exclude those records from the needed IDs that have Codes that are not unique or also belong to other IDs.

The result is a table that contains only needed IDs and in this table every Code is unique.

Upvotes: 1

HansUp
HansUp

Reputation: 97131

You can identify which codes are unique with a query which uses GROUP BY and HAVING.

SELECT [Code]
FROM YourTable
GROUP BY [Code]
HAVING Count(*) = 1;

To get full rows which match those unique [Code] values, join that query back to your table.

SELECT y.*
FROM
    YourTable AS y
    INNER JOIN
    (
        SELECT [Code]
        FROM YourTable
        GROUP BY [Code]
        HAVING Count(*) = 1
    ) AS sub
ON y.Code = sub.Code;

Upvotes: 5

Related Questions