DECLARE @temp table
(eno int,
eid int)

INSERT INTO @temp VALUES (1, 1)
INSERT INTO @temp VALUES (2 ,2)
INSERT INTO @temp VALUES (3, 3)
INSERT INTO @temp VALUES (4, 3)
INSERT INTO @temp VALUES (5 ,3)
INSERT INTO @temp VALUES (6 ,4)
INSERT INTO @temp VALUES (7, 4)
INSERT INTO @temp VALUES (8 ,4)
INSERT INTO @temp VALUES (9 ,4)
INSERT INTO @temp VALUES (10, 5)
INSERT INTO @temp VALUES (11, 5)
INSERT INTO @temp VALUES (12, 5)

select *  from @temp 



SELECT r.eno,r.eid
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY t.eid ORDER BY t.eno) AS RowNo,
t.eid,
t.eno
FROM @temp t
INNER JOIN (SELECT eid,COUNT(eno) AS empcount
            FROM @temp GROUP BY eid)tmp
ON t.eid=tmp.eid
WHERE tmp.empcount >1
)r
WHERE r.RowNo <=2


output
---------
input values:-
eno         eid
----------- -----------
1           1
2           2
3           3
4           3
5           3
6           4
7           4
8           4
9           4
10          5
11          5
12          5

result:-

eno         eid
----------- -----------
3           3
4           3
6           4
7           4
10          5
11          5

quote:

---

Originally posted by visakh16

Can you explain how you expect to get this o/p?

---

SELECT	eno,
	eid
FROM	(
		SELECT	eno,
			eid,
			ROW_NUMBER() OVER (PARTITION BY eid ORDER BY eno) AS RecID,
			COUNT(*) OVER (PARTITION BY eid) AS Records
		FROM	Emp
	) AS d
WHERE	RecID < 3
	AND Records > 1

quote:

---

Originally posted by NarayanaMoorthy

quote:

---

Originally posted by visakh16

Can you explain how you expect to get this o/p?

---

Hi,
I have created a table
CREATE TABLE emp(eno int identity(1,1), eid int)

Inserted values

INSERT INTO emp VALUES (1)
INSERT INTO emp VALUES (2)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (3)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (4)
INSERT INTO emp VALUES (5)
INSERT INTO emp VALUES (5)
INSERT INTO emp VALUES (5)

What i have tried
select max(eid) from emp group by eid having count(eid)>1
not getting desired output.

My result should be like this
eno eid
----------- -----------
3 3
4 3
6 4
7 4
10 5
11 5

Narayana Moorthy

---