Requires Free Membership to View
The first thing to do is to write a query to to determine the top 3 rows of Table1. Then you would join this to Table2 to get the final results you want.
In my previous answer FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows... , you will see that I recommend using the special feature unique to your database system, e.g. TOP or LIMIT, to make retrieval of the top rows efficient. In any case, you could use the generic solution given at the end. Your situation is slightly complicated by the fact that you need first to count the rows by emp_id:
select DT.emp_id
, DT.rows
from ( select emp_id
, count(*) as rows
from Table1
group
by emp_id
) as DT
where ( select count(*)
from ( select emp_id
, count(*) as rows
from Table1
group
by emp_id
) as DT2
where rows > DT.rows ) < 3
If you compare this query to the generic solution, you'll see that "yourtable" has been replaced in two places by a derived table (subquery) that does the counting.
Okay, now that we know which are the top 3 emp_id values, all that remains is to join to Table2:
select DT.emp_id
, DT.rows
, Table2.last_name
, Table2.first_name
from ( select emp_id
, count(*) as rows
from Table1
group
by emp_id
) as DT
inner
join Table2
on DT.emp_id = Table2.emp_id
where ( select count(*)
from ( select emp_id
, count(*) as rows
from Table1
group
by emp_id
) as DT2
where rows > DT.rows ) < 3
order
by DT.rows desc
Looks complicated when you see the final solution, but it's really pretty straightforward once you know how to write a "Top N" query using a derived table.
This was first published in June 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation