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)
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.
SELECT * FROM <TableName>; This SQL query will select all columns and all rows from the table. For example: SELECT * FROM [Person].
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.
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
Something like this should work:
SELECT idA, COUNT(*) FROM XXX GROUP BY idA HAVING COUNT(*) > 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With