Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select non "unique" rows

Tags:

sql

mysql

I have the following table, from which i have to fetch non unique rows

id idA infos
0 201 1899
1 205 1955
2 207 1955
3 201 1959

I'd like fetch all the rows for the column infos, that have a same idA value in at least two rows.

Output of the query for the above table must be

infos 1899 1959  

I've tried the following requests with no success :

  • SELECT idA FROM XXX WHERE NOT EXISTS(SELECT * FROM XXX GROUP BY idA)
  • SELECT * FROM XXX a WHERE NOT EXISTS(SELECT * FROM XXX b WHERE a.RVT_ID=b.RVT_ID GROUP BY idA)
like image 486
AdrienG Avatar asked Dec 23 '10 14:12

AdrienG


People also ask

How do I select only unique rows in SQL?

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

How do I select all rows in SQL?

SELECT * FROM <TableName>; This SQL query will select all columns and all rows from the table. For example: SELECT * FROM [Person].

How do I filter duplicate records in SQL?

The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.


2 Answers

Try this:

SELECT T1.idA, T1.infos FROM XXX T1 JOIN (     SELECT idA     FROM XXX     GROUP BY idA     HAVING COUNT(*) >= 2 ) T2 ON T1.idA = T2.idA 

The result for the data you posted:

 idaA  infos 201   1899 201   1959 
like image 116
Mark Byers Avatar answered Oct 04 '22 10:10

Mark Byers


Something like this should work:

SELECT idA, COUNT(*) FROM XXX GROUP BY idA HAVING COUNT(*) > 1 
like image 37
judda Avatar answered Oct 04 '22 11:10

judda