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.
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_numberenquiry.enquiry_timeofficer.officer_nameWhat 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 |
|
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? |
|
|
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 columnSELECT 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 |
|
|
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. |
|
|
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? |
|
|
|
|
|