SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Restrict results based on Top 10 people with Enq?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 09/26/2013 :  07:28:01  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 09/26/2013 :  07:42:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 09/27/2013 :  09:23:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/27/2013 :  09:43:34  Show Profile  Reply with Quote
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 - 10/01/2013 :  04:36:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/01/2013 :  09:38:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000