Q

Top 3 counts querying two tables

I need to query two tables and return the top 3 counts. For example, in Table1 I have the field emp_id(fk), in Table2 I have emp_id(pk), last_name, first_name. I'm trying to create a query that will show the names of the employees who appeared the most times (top 3 only) in Table1. Any ideas?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close