CODEWITHSUNDEEP
sqlpostgresql
st2 tas
st2 tas

Reputation: 91

Fastest way to check for unique entries in Postgres

I have a table that looks something like this: 

first | last
John  | Smith
Bob   | dfgdf
John  | fggf
John  | Smith

And I want to run a query that will return only rows that have a unique last name for each first name. So only Bob dfgdf should be returned. Currently, I'm grouping twice and checking if count = 1, but is there a faster way?

SELECT first FROM ( SELECT first, last FROM table1 GROUP BY first, last )as t1 GROUP BY first HAVING COUNT(*) = 1

Upvotes: 0

Views: 1654

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would do this as:

SELECT first
FROM table1
GROUP BY first
HAVING MIN(last) = MAX(last);

Actually, this should make use of an index on table1(first, last).

If the above doesn't use the index, then I would expect the fastest way to be:

select distinct on (first) first
from table1 t1
where not exists (select 1 from table1 tt1 where tt1.first = t1.first and tt1.last <> t1.last)
order by first;

This can make use of an index on table1(first, last) for performance.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Try this version:

SELECT first
FROM table1
GROUP BY first
HAVING COUNT(*) = COUNT(DISTINCT last);

Demo

This just retains only first names whose record count is coincident with the count of distinct last names, which would imply that each first name maps to a distinct last name.

Edit:

If you want all columns from all matching rows, then you may try:

WITH cte AS (
    SELECT first
    FROM table1
    GROUP BY first
    HAVING COUNT(*) = COUNT(DISTINCT last)
)

SELECT t1.*
FROM table1 t1
INNER JOIN cte t2
    ON t1.first = t2.first;

Upvotes: 1

Related Questions