need a simple query in t-sql

I have a query that I have simplified for 开发者_StackOverflow社区our purpose. How do you achieve this result ?

ID OrigId
----------
1   1
2   1
3   3
4   4
5   4
6   6

Result

ID OrigId
----------
1   1
2   1
4   4
5   4

---

To bring back all rows where the corresponding OrigId appears more than once in the table you can use

;WITH CTE AS
(
SELECT *,
       COUNT(*) OVER (PARTITION BY OrigId) AS C
FROM YourTable
)
SELECT ID,
       OrigId
FROM CTE
WHERE C >1

---

You can use a HAVING statement

SELECT *
FROM dbo.Table
WHERE OrigID IN
(
SELECT OrigID
FROM dbo.Table
GROUP BY OrigID
HAVING COUNT(*) > 1
)

---

select *
from selecttest
where origid in
(
    select origid
    from selecttest
    group by origid
    having COUNT(*) > 1
)