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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading