I fully realize that this thread is 3-1/2 years old and the OP probably got a solution a very long time ago. I found this thread totally by accident while I was looking for something else. Since it was never answered, I though I'd take the time to provide a solution in case someone else might be looking for something similar.
This is simply a "Select top 2 from each group" problem with the added caveat that the groups we're selecting from must have at least 3 items. Here's the solution with some test data. As is normal, the details are in the comments in the code...
-- Create and populate a test table using the data from the original post.
-- Nothing in this section is a part of the solution to the problem.
-- We're just building test data here.
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
--===== Create the test table (no indexes included for this test)
CREATE TABLE #MyHead
--===== Populate the test table with data from the original post
INSERT INTO #MyHead
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,4 UNION ALL
SELECT 7,4 UNION ALL
SELECT 8,4 UNION ALL
SELECT 9,4 UNION ALL
SELECT 10,5 UNION ALL
SELECT 11,5 UNION ALL
-- One possible solution to the problem.
--===== Return the top 2 from each group having at least 3 rows in the group
FROM #MyHead mh1
WHERE eno IN ( --=== Find the eno's for only two rows for each eid
SELECT TOP 2
FROM #MyHead mh2
WHERE mh2.eid = mh1.eid
AND mh1.eid IN ( --==== Find only those eid's that have at least 3 rows
GROUP BY eid
HAVING COUNT(*) >= 3
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs