How to Find Duplicate Rows with Different Values in a Specific Column?

Selecting Duplicate Rows with Divergent Column Values

This database query aims to identify duplicate rows based on a specific column while disregarding the values of other columns. Consider the following scenario:

 ------ ------ </p>
<table>
<thead><tr>
<th>ARIDNR</th>
<th>LIEFNR</th>
</tr></thead>
<tbody>
<tr>
<td>1</td>
<td>A</td>
</tr>
<tr>
<td>2</td>
<td>A</td>
</tr>
<tr>
<td>3</td>
<td>A</td>
</tr>
<tr>
<td>1</td>
<td>B</td>
</tr>
<tr>
<td>2</td>
<td>B</td>
</tr>
</tbody>
</table>
<p>

The objective is to select the ARIDNR values that appear more than once with distinct LIEFNR values.

Solution

To achieve this, we can utilize the following query:

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

This query employs a nested subquery to identify the ARIDNR values that meet the specified criteria. The subquery groups rows by ARIDNR and applies the COUNT(DISTINCT LIEFNR) function, which calculates the number of unique LIEFNR values associated with each ARIDNR. If the count exceeds 1, the ARIDNR is added to a temporary table.

The main query then selects all rows from the original table where the ARIDNR values match those identified by the subquery. This results in the desired output:

 ------ ------ </p>
<table>
<thead><tr>
<th>ARIDNR</th>
<th>LIEFNR</th>
</tr></thead>
<tbody>
<tr>
<td>1</td>
<td>A</td>
</tr>
<tr>
<td>1</td>
<td>B</td>
</tr>
<tr>
<td>2</td>
<td>A</td>
</tr>
<tr>
<td>2</td>
<td>B</td>
</tr>
</tbody>
</table>
<p>

This method effectively captures the duplicate rows with varying LIEFNR values, meeting the initial requirement.

The above is the detailed content of How to Find Duplicate Rows with Different Values in a Specific Column?. For more information, please follow other related articles on the PHP Chinese website!