SQL Server SQL Query to select duplicates without grouping [duplicate]

wz1wpwve 于 2024-01-05 发布在其他

关注(0)|答案(2)|浏览(131)

This question already has answers here:

SELECT Full Row with Duplicated Data in One Column (4 answers)
Check which all records have duplicate using window function (1 answer)

Closed last month.

I want to find rows in my table where both power and price match some other row in the table.

+----+-------+-------+--------+
| id | Power | Price | Name   |
+------------+-------+--------+
| 1  | 300W  | $5.00 | Alpha  |
| 2  | 500W  | $4.00 | Kilo   |
| 3  | 500W  | $3.00 | Whiskey|
| 4  | 300W  | $5.00 | Charlie|
| 5  | 400W  | $4.00 | Oscar  |
+----+-------+-------+--------+

I want the resulting table to include all columns, including the primary key, and I don't want them grouped.

+----+-------+-------+--------+
| id | Power | Price | Name   |
+------------+-------+--------+
| 1  | 300W  | $5.00 | Alpha  |
| 4  | 300W  | $5.00 | Charlie|
+----+-------+-------+--------+

What can I do to find duplicates without any of the columns or duplicate rows being eliminated?

sql-server

来源：https://stackoverflow.com/questions/77573829/sql-query-to-select-duplicates-without-grouping

2条答案

按热度按时间

2w3rbyxf1#

If your RDBMS support window functions, you can use function COUNT() , then select only the records having count higher than 1 :

select id, Power, Price, Name
from (
  select *, count(*) over (partition by Power, Price) as cnt
  from mytable
) as s
where cnt > 1;

Demo here

赞(0）回复(0）举报 2024-01-05

kkih6yb82#

Basic doubloon search :

SELECT *
FROM mytable t1
WHERE EXISTS (
    SELECT *
    FROM mytable t2
    WHERE t1.id <> t2.id
    AND t1.Power = t2.Power
    AND t1.Price = t2.Price
);

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server SQL Query to select duplicates without grouping [duplicate]

2条答案

相关问题

热门标签

最新问答