SQL: Filter out rows where column value occurs more than once

select rows where column contains same data in more than one record
sql select values that occur more than once
sql select duplicate rows based on one column
sql select where more than one occurrence
sql how to show row with same column value only once
sql select count of rows with same values in one column
sql count
how to select all rows which have same value in some column

I have a MySQL table that looks like this:

id    |    label
----------------
1          "john"
1          "henry"
1          "sara"
2          "henry"
3          "tim"

So a given id can have multiple labels. I want to keep only the rows where the id has a single label. So the correct output for the above table would be:

id    |    label
----------------
2          "henry"
3          "tim"

I was thinking I should group by id and find the count of labels for each id. Then I'd take only rows with a count of 1.

WITH temp as
(SELECT id
FROM original_table
GROUP BY id
HAVING COUNT(id) > 5)

SELECT *
FROM original_table ot
WHERE ot.id in temp.id

Does that look close?

Thanks!

You could just use a join to only include ID's that occur once in a sub-query:

SELECT  id,
        label
  FROM  original_table ot
    INNER JOIN  (
                SELECT  id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
                ) a ON a.id = ot.id;

Or you could use an IN clause:

SELECT  id,
        label
  FROM  original_table
  WHERE id IN (SELECT   id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
              );

Select all rows where a column value occurs more than once , Your query seems correct (although it may not be the most efficient). There are many ways to write this type of query. For example, you could  SQL Query To Obtain Value that Occurs more than once. and Johnson since these values occur more than or equal to 3 times. Capturing SQL row changes (multiple

I think aggregation is the simplest method:

select id, min(label) as label
from original_table t
group by id
having count(*) = 1;

select rows where column contains same data in more than one record, How do I check if two rows have the same value in SQL? SQL Query - Selecting records that repeat / occur more than once in a table -- newbe question. Oracle Database Forums on Bytes.

You could try this:

SELECT t.id, t.label
FROM tbl AS t
JOIN (SELECT id FROM tbl GROUP BY id HAVING count(label) = 1) AS t1
ON t.id = t1.id;

Selecting all unique rows from duplicate rows in table in SQL Server , How do I get the same values in the same column in SQL? And 7.8 million rows are 39 thousand times more than 200, which is pretty close. At the time I fired these queries we had only 43 million of articles. PostgreSQL gathers some statistics about values in different columns to be able to produce reasonable query plans, and looks like it's shooting blanks for this column.

Assuming that the pair id and label is unique, you can use NOT EXISTS and a correlated subquery.

SELECT t1.id,
       t1.label
       FROM original_table t1
       WHERE NOT EXISTS (SELECT *
                                FROM original_table t2
                                WHERE t2.id = t1.id
                                      AND t2.label <> t1.label);

Only select rows where item occurs more than once, How do I select a single record for duplicates in SQL Server? Use COUNT() to return the number of times the same value occurs. The column reference is a powerful tool, but it does limit us a bit: You can't use the empty cells in column B below or above the

Yes your approach is correct and you may have to change your having count condition and while referring to CTE you may have to change your syntax little , but you can do it without CTE as well in the same line with exists condition.

Create table  temp  (ID int , Label varchar(10)); 

insert into  temp values 
(1  ,      "john" ), 
(1   ,     "henry" ) , 
(1   ,     "sara" )  , 
( 2   ,    "henry"  ) , 
(3   ,     "tim" ) ; 


select t.ID , t.Label from temp t 
where exists (
select ID, count(1) Dups  from temp t1 where t1.ID = t.ID group by ID having count(1) 
= 1) 

Output:

    ID, Label
    2, henry
    3, tim

Finding Duplicates with SQL, How do you eliminate duplicate values in db2 select of a single table? ASP.NET Forums / Data Access / SQL Server, SQL Server Express, and SQL Compact Edition / Only select rows where item occurs more than once Only select rows where item occurs more than once [Answered] RSS

Documentation: 9.3: SELECT, I have the following simple SQL statement: SELECT Order_Num, Order_Date, Now I only want to select rows where there is more than one order per Cust_Num​. inner join orderheader a on a.cust_num=b.cust_num numeric(18,2)) insert into @OrderHeader values (1,GETDATE(), 1),(2,DATEADD(YY  The SQL language has a number of ways to filter record sets. The “where” clause in your select statements is where most people list the business rules that filter out records. You can use “JOIN” statements with SQL in them, but these are usually more difficult to read. The “NOT EXISTS” statement uses a subquery …

Pro EJB 3: Java Persistence API, You could also use a variant of this SQL to find rows that occur exactly once: To Get Duplicate Values form table do this: add one more identity column in table. If two rows having duplicates, then there is an easy solution to find it out. Hi, I need to filter the records based on the unique combination 3 fields eg: in source  How to Filter Query Results In today’s lesson, you’re going to learn how to filter query results using the WHERE clause . This clause is important as only those records matching the where clause’s conditions are returned in the query results.

How to find duplicate records in a table on database, A WITH query that is referenced more than once in FROM is computed only once. the output is combined into groups of rows that match on one or more values. You must have SELECT privilege on each column used in a SELECT command. rows before the application of GROUP BY, while HAVING filters group rows  To explain, in row 2, I have all the different shifts of said person (0700 - 1500, 0700 - 1430) etc. My main issue is that i need a column that counts the total hours of all the shifts of a person (column C in this example). The previous person before me would manually input the hours in all the shifts, and then just had a sum in that column.

Comments
  • What happens when you try the query? Do you get the results you want?
  • I'd use a simple LEFT JOIN - an exclusion join. Your solution seems unnecessarily complicated