How to get unique set of rows from SQL where uniqueness is defined by 2 columns?

Question

If I have a table as follows:

ID | Name | Category | Parent | URL | LastModified

If two rows have the same Name and parent, then they are not unique. How do I get the unique set of rows in this case (but return more than the columns that make them unique)?

So, for more details: This is a corporate keywords table, where keywords are organized by category. Each keyword can only have one category. Each keyword can have child keywords, so if parent=0 or NULL, it is a root keyword. If keywords have the same name and parent in a category, then they are not unique (regardless of the other columns). If two keywords have the same name and category, and parent=0 or NULL then they are not unique. If there are duplicates, then I only want the 1st one. The reason is I am putting these into a system that will not allow a keyword to have two children with the same name.

I would also like to see what rows ARE duplicates to see what is causing me the trouble!

Thanks a million so far for excellent responses. I am obviously not a SQL guy... :(

Answer 1

It depends on what you want to do with non-unique rows. If you want to not have them in the result set you could use group by and having:

select Name, Parent, Max(Category) 
from Table
group by Name, Parent
having count(*) = 1

You need the Max(Category) because you aren't grouping by that column, even though there will only be one row per Name and Parent.

If, though, you want to include non-unique rows in the result, similar to:

select distinct Name, Parent, Category from Table

except that two rows with the same Name and Parent but different Category only return a single row. In that case you need to decide what to show for Category, since more than one row will be condensed down to one. You could still use Max(Category) or Min(Category) and group by, but leave off the having.

select Name, Parent, Max(Category) 
from Table
group by Name, Parent

Answer 2

This query finds all rows where no other row has the same name and parent. If two rows have parent set to NULL, these rows are not considered to have the same parent.

SELECT T1.*
FROM Table1 T1
LEFT JOIN Table1 T2
ON T1.ID != T2.ID AND T1.Name = T2.Name AND T1.Parent = T2.Parent
WHERE T2.ID IS NULL

Answer 3

You could use the row_number function to partition by Name and Parent, like:

select *
from (
    select
        row_number() over (partition by Name, Parent 
                           order by Name, Parent) as rn
    ,   *
    from YourTable
) sub
where rn = 1 -- Only first row for a name/parent combination

If you're looking to select only rows that are unique, in the sense that no other rows with the same name and parent exist, try:

select *
from YourTable a
where (
    select count(*)
    from YourTable b
    where a.Name = b.Name
    and a.Parent = b.Parent
) = 1

Answer 4

select x,y,z
from tablename t1
where not exists (select 1 from tablename t2 where t2.name = t1.name and t1.parent = t2.parent and t2.id <> t1.id)

may run slow depending on table size