How to Select Rows with Duplicate IDs but Different Values in SQL?

Selecting Rows with Duplicate IDs and Distinct Values

You encounter a data management challenge in which you possess a table containing IDs (ARIDNR) and corresponding values (LIEFNR). Your objective is to identify and select all rows with duplicate IDs but differing values in the LIEFNR column.

Solution:

To accomplish this task, utilize the following SQL query:

SELECT *
FROM Table
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM Table
    GROUP BY ARIDNR
    HAVING COUNT(distinct LIEFNR) > 1
)

Explanation:

• The outer query (SELECT *) retrieves all columns for rows that meet the specified condition.
• The inner query (SELECT ARIDNR) selects the unique ARIDNR values.
• The GROUP BY clause groups the rows by the ARIDNR column.
• The HAVING clause filters the results to include only groups with more than one distinct LIEFNR value.

This query effectively isolates rows with duplicate ARIDNRs and varying LIEFNR values. Upon execution, it will produce the desired output:

+------+------+
| ARIDNR | LIEFNR |
+------+------+
| 1     | A     |
+------+------+
| 1     | B     |
+------+------+
| 2     | A     |
+------+------+
| 2     | B     |
+------+------+

This solution efficiently addresses your requirement and provides a reliable method for identifying and selecting rows with the specified criteria.

The above is the detailed content of How to Select Rows with Duplicate IDs but Different Values in SQL?. For more information, please follow other related articles on the PHP Chinese website!