Select rows where string does not repeat

Question

I'm not sure how to phrase this, but I have two tables that I use INNER JOIN on to count the number of records I have. This works fine but the problem is that I have some rows in table1 where some of the records have a string that can appear in another record. Something like this:

table1                   table2
------                   ------
id    string             id    table1_id    some_column
01    aaa                01    01           1
02    bbb                02    02           3
03    aaa                03    03           1
04    ccc                04    04           4
05    bbb                05    05           2
...                      ...

My query looks like this:

SELECT COUNT(*) FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id

This query works fine, but I want to be able to get distinct values. So my query should bring me back only these records:

table1                   table2
------                   ------
id    string             id    table1_id    some_column
01    aaa                01    01           1
02    bbb                02    02           3
04    ccc                04    04           4
...                      ...

As you can see, it doesn't show any additional records where they share the same string. What would I write after or before my INNER JOIN? Any help would be appreciated.

Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](http://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — PM 77-1, Mar 31 '17 at 16:55

score 1 · Accepted Answer · answered Mar 31 '17 at 16:55

1

You can group by the table 1

select count(*) 
from (
    select min(id) as id, string 
    from table1 
    group by string
) t1
inner join table2 on t1.id = table2.table1_id

answered Mar 31 '17 at 16:55

ScaisEdge

131,976
10
91
107

score 1 · Answer 2 · answered Mar 31 '17 at 16:58

using common table expression with row_number() to select the top 1 per group of string.

;with cte as (
  select *
    , rn = row_number() over (partition by string order by id)
  from t1
)

select count(*)
  from cte
    inner join t2
      on cte.id = t2.table_1id
     and cte.rn = 1

using a subquery instead of the cte:

select count(*)
  from (
    select *
      , rn = row_number() over (partition by string order by id)
    from t1
  ) sub
    inner join t2
      on sub.id = t2.table_1id
     and sub.rn = 1

Thanks for letting me know about common table expressions and how to use it! Looks very handy. — o.o, Mar 31 '17 at 17:05

Das Nuk · Answer 3 · 2017-03-31T17:03:04.557

0

SELECT DISTINCT count(*) from table1 inner join table2 on table1.id = table2.table1_id

This will give you the count of distinct values in the tables

edited Mar 31 '17 at 17:03

answered Mar 31 '17 at 16:56

Das Nuk

46
8

Thank you, however, this still returns all the records. – o.o Mar 31 '17 at 17:04

score 0 · Answer 4 · answered Mar 31 '17 at 21:31

If you really only want the count you can do this:

SELECT COUNT(DISTINCT table1.string)
FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id

Some of the other answers are clearly better if you want do do something with the distinct values other than just count them.

Select rows where string does not repeat

4 Answers4