CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlgroup-byhaving-clause
Saygın Karahan
Saygın Karahan

Reputation: 177

Having trouble with grouping rows - MS SQL 2008

I have a product table which has some duplicate records. I need to get primarykeys atfer grouped them according to names and types

DECLARE @Products TABLE 
(
    pkProductId INT,
    productName NVARCHAR(500),
    productType INT
)

INSERT INTO @Products (pkProductId, productName, productType)
VALUES
(1, 'iphone', 0),
(2, 'iphone', 0),
(3, 'iphone', 1),
(4, 'iphone', 1),
(5, 'iphone', 1)

After I run like tsql

SELECT pr.pkProductId FROM @Products pr
GROUP BY pr.productName, pr.productType
HAVING COUNT(pr.productName) > 1

I Want To Get These IDs

pkProductId
---------------
2
4
5

Thank You For Your Hepls :)

Upvotes: 0

Views: 66

Answers (1)

Taryn
Taryn

Reputation: 247680

You could use row_number() to get the result:

select pkProductId
from
(
  select pkProductId,
    productName, 
    productType,
    row_number() over(partition by productName, productType order by pkproductId) rn
  from @Products
) d
where rn >1;

See SQL Fiddle with Demo

Upvotes: 2

Related Questions