Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Restrict results based on Top 10 people with Enq?

Author  Topic 

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-09-26 : 07:28:01
Hi all,

I am trying to write a report that gives me a list of all enquiries based on top 10 people who received it the most.

The fields are:

enquiry.enquiry_number
enquiry.enquiry_time
officer.officer_name

What I want to do is run the report which details each enquiry each officer has received but stop running once top 10 officers have the most enquiries are in the results.

The officers are identified using the officer.officer_name field, enquiry.enquiry_number field shows each enquiry record.

Anyone able to share a way to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-26 : 07:42:22
May this help you? http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-09-27 : 09:23:46
Hi Madhivanan,

Sorry but I couldn't see how I could apply those examples to my query. Would you be able to shown an illustrated example?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 09:43:34
Something like this? Not clear to me what the column that is common to enquiry and officer tables are. You will need to join on that column

SELECT a.*
FROM enquiry a
INNER JOIN
(
SELECT TOP (10)
o.officer_name
FROM
officer o
INNER JOIN enquiry e ON
e.officer_name = e.officer_name -- or some other column to join
GROUP BY
o.officer_name
ORDER BY
COUNT(*) DESC
) b ON a.officer_name = b.officer_name -- or some other column to join
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-10-01 : 04:36:52
Hi James,

This still doesn't quite work, if it helps the join between enquiry table and officer table is officer_code.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 09:38:43
If you join on the officer_code, what does it do? Does it give the wrong results, or no results at all, or does it do something else?
Go to Top of Page
   

- Advertisement -