3

Given the table

| id | Name |
| 01 | Bob  |
| 02 | Chad |
| 03 | Bob  |
| 04 | Tim  |
| 05 | Bob  |

I want to select the name and ID, from rows where the name is unique (only appears once)

This is essentially the same as How to select unique values of a column from table?, but notice that the author doesn't need the id, so that problem can be solved by a GROUP BY name HAVING COUNT(name) = 1

However, I need to extract the entire row (could be tens or hundreds of columns) including the id, where COUNT(name) = 1, but I cannot GROUP BY id, name as every combination of those are unique.

EDIT:

Am using Google BigQuery.

Expected results:

| id | Name |
| 02 | Chad |
| 04 | Tim  |
jarlh
  • 42,561
  • 8
  • 45
  • 63
Olsgaard
  • 1,006
  • 9
  • 19
  • 1
    "SQL" is just a language, not an actual product. Please tell us which _version_ of SQL you are using (e.g. MySQL, SQL Server, Oracle, Postgres, etc.). – Tim Biegeleisen Mar 19 '19 at 09:16
  • It's always a good idea to specify the expected result as well, even when you find it obvious. – jarlh Mar 19 '19 at 09:17
  • Thank you for your comments, I have updated the question with SQL-version and expected results. – Olsgaard Mar 19 '19 at 10:47

8 Answers8

6

Simply do a GROUP BY. Use HAVING to make sure a name is only there once. Use MIN() to pick the only id for the name.

select min(id), name
from tablename
group by name
having count(*) = 1

Reading the table only once will increase performance! (And don't forget to create an index on (name, id).)

jarlh
  • 42,561
  • 8
  • 45
  • 63
2

Use correlated subquery

DEMO

select * from tablename a
where not exists (select 1 from tablename b where a.name=b.name having count(*)>1)

OUTPUT:

id  name
2   Chad
4   Tim
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • 1
    It just beyond my understanding who is downvoting me without even understanding the answer - if you think you've enough knowledge - explain me – Fahmi Mar 19 '19 at 09:22
  • 1
    I haven't voted your answer, but I noticed that in your output Tim is not mentioned. Maybe you have forgotten about (EDIT: writing down) him? – Czarek Mar 19 '19 at 09:32
  • @Czarek, have you checked the demo - go there add another row for tim - it will come – Fahmi Mar 19 '19 at 09:33
  • If you update your post to include all rows in the fiddle and the same code in your example as the fiddle, I'll mark this as accepted – Olsgaard Mar 19 '19 at 12:38
2

You can use NOT EXISTS :

SELECT t.*
FROM table t
WHERE NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.name = t.Name AND t1.id <> t.id);

This would need index on table(id, name) to produce faster result set.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
1

How about a simple aggregation?

select any_value(id), name
from t
group by name
having count(*) = 1;

BigQuery works quite well with aggregations so this might be quite efficient as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

use exists and check uqique name 

   select id,name 
    from table t1 
   where exists ( select 1 from table t2 where t1.name=t2.name

    having count(*)=1
)
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

Please try this.

SELECT 
   DISTINCT id,NAME
FROM
   tableName
Hemang A
  • 1,012
  • 1
  • 5
  • 16
0

You can use multiple subqueries to extract what you need. 

SELECT * FROM tableName
WHERE name IN (SELECT name FROM (SELECT name, COUNT(name) FROM tableName
                                 GROUP BY name 
                                 HAVING COUNT(name) = 1) AS subQuery)
Czarek
  • 689
  • 9
  • 20
0

Below is for BigQuery Standard SQL and works for any number of columns w/o explicitly calling them out and does not require any join'ing or sub-selects 

#standardSQL
SELECT t.*
FROM (
  SELECT ANY_VALUE(t) t
  FROM `project.dataset.table` t
  GROUP BY name
  HAVING COUNT(1) = 1
)
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230