select 5 records per call type

[AlanKW]

I have a call-tracking database. Each call can be catagorized by 1 or 12 different ways (CallType) Sr Management wants to see a sampling of 5-10 calls per call-type. Unfortunately Topx doesn't work this way.

Thoughts anyone?

 

[PHV]

Brute force method:
SELECT TOP 5 * FROM yourTable WHERE CallType=1 UNION
SELECT TOP 5 * FROM yourTable WHERE CallType=2 UNION
...
SELECT TOP 5 * FROM yourTable WHERE CallType=11 UNION
SELECT TOP 5 * FROM yourTable WHERE CallType=12

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mp9]

How about a series of TOP 5 queries joined together with UNION... sort of like

SELECT TOP 5 CallId as IdNumber, Description...
FROM tblCalls
WHERE CallType = 1
ORDER BY CallID
UNION SELECT TOP 5 CallId, Description...
FROM tblCalls
WHERE CallType = 2
ORDER BY CallID
UNION SELECT...

Obviously I've second-guessed table and field names here, but you get the idea.

More on UNION:

http://www22.brinkster.com/accessory/queries/002.shtml

More on TOP n queries:

http://www22.brinkster.com/accessory/queries/004.shtml

Helpful?

 

[PHV]

Another way:
SELECT A.*
FROM yourTable A
WHERE A.PrimaryKey In (SELECT TOP 5 B.PrimaryKey FROM yourTable B WHERE B.CallType = A.CallType);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AlanKW]

I like where PHV's second response is going. Here is my actual code

SELECT tbl_Complaint.*
FROM tbl_Complaint
WHERE (((tbl_Complaint.ComplaintID) In (SELECT TOP 5 tlkp_ComplaintCatagories.ComplaintID FROM tlkp_ComplaintCatagories WHERE tbl_Complaint.ComplaintType = tlkp_ComplaintCatagories.ComplaintID)));

which brings up no cases. If the WHERE line begins with (((tbl_Complaint.ComplaintType then I get all cases.

I now realize that I have complaint ID as the primary key in both tbl_complaint and tlkp_ComplaintCatagories which is bad and unintentional. That'll be on my Todo List..

 

[PHV]

My suggestion was:
SELECT A.*
FROM tbl_Complaint A
WHERE A.ComplaintID In (SELECT TOP 5 B.ComplaintID FROM tbl_Complaint B WHERE B.ComplaintType = A.ComplaintType);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Alan,

A little more info:

PHV is using a correlated subquery,

WHERE B.ComplaintType = A.ComplaintType

So that the complaint type in the subquery has to match the complaint type in the main query in order for the records to be returned.

Leslie

 

[AlanKW]

Fantastic. Thank you. If I was confident that they were only going to ask me once, I'd do it by hand. But you know how management is...