How to Select Rows with Duplicate IDs and Unique Column Values in SQL?

Extracting Rows with Duplicate IDs and Distinct Column Values

The Query:

Consider the following table:

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

The goal is to retrieve all rows where the ARIDNR column has duplicate values while the LIEFNR column has distinct values.

Solution:

To execute this query effectively, follow these steps:

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

Explanation:

This query utilizes a subquery to identify ARIDNRs that occur more than once. In the outer query, it retrieves all rows with the identified ARIDNRs.

1. Subquery:

   • It groups the rows by ARIDNR.
   • The HAVING clause filters for ARIDNRs with more than one distinct LIEFNR value.

2. Outer Query:

   • The main query filters the table to include only rows whose ARIDNRs satisfy the subquery condition.

Result:

The output of the query will be:

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

This result successfully captures all rows with duplicate ARIDNRs and distinct LIEFNR values.

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