Select Top 5 For Top 5

[rubbernilly]

Hello, all,

I am returning the top 5 product groups in a query. No problem. Now, for each of these 5 product groups, I would like to return the top 5 products contributing towards making the groups the top 5 of the groups. In the end, what I want is:

Group1 - Product1/1
Group1 - Product1/2
Group1 - Product1/3
Group1 - Product1/4
Group1 - Product1/5
Group2 - Product2/1
Group2 - Product2/2
Group2 - Product2/3
etc...

Is there a way to do this via a query rather than code?

 

[PHV]

And what have you tried so far ?
There is 2 common ways to achieve this:
1) Correlated TOP 5 subquery
2) Aggregated theta join having count(*)<=5

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

 

[rubbernilly]

Hey, PHV, thanks for the feedback.

Here is what I have tried...

I have a query called adm_T5_5DigPN_Repairs (5DigPN is the Group), that returns the Top 5 5DigPN contributing to a single Vehicle Line's repair numbers. It is based on another query called adm_WarrantyQuery, which is a basic SELECT query of all of the warranty records I have in the database for all Vehicle Lines.

I have tried the following in a new query:

SELECT TOP 5 a.VehicleName, a.ProductID, Count(a.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS a
WHERE a.[5DigPN] In (SELECT [5DigPN] FROM adm_T5_5DigPN_Repairs as b)
GROUP BY a.VehicleName, a.ProductID
ORDER BY Count(a.ClaimID) DESC;

I also tried that same query making it correlative, adding a "WHERE a.[5DigPN] = b.[5DigPN]" to the subquery, but both with and without that bit, the query looked as if it was going to take forever to run. After 5 minutes, I cancelled the query progress.

Is that the sort of the correlative option you were describing? If so, what am I doing wrong?

I have tried adding the adm_T5_5DigPN_Repairs query to a new query and linking it to the adm_WarrantyQuery on VehicleName and 5DigPN. Is this where I would make use of the theta join, or in another query after/in place of this one?

Thanks again for your help.

 

[PHV]

Not knowing the relation between VehicleName and 5DigPN, just a guess with a correlated subquery:
SELECT A.VehicleName, A.ProductID, Count(A.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS A
INNER JOIN adm_T5_5DigPN_Repairs AS B ON A.[5DigPN] = B.[5DigPN]
GROUP BY A.VehicleName, A.ProductID
HAVING Count(A.ClaimID) IN (SELECT TOP 5 Count(T.ClaimID) FROM adm_WarrantyQuery AS T
WHERE T.VehicleName = A.VehicleName AND T.ProductID = A.ProductID ORDER BY 1 DESC)

If that don't suit your needs, posting the SQL code of adm_T5_5DigPN_Repairs may help.

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

 

[rubbernilly]

Here is the SQL for the adm_T5_5DigPN_Repairs query:

SELECT TOP 5 adm_WarrantyQuery.VehicleName, adm_WarrantyQuery.[5DigPN], Count(adm_WarrantyQuery.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery
WHERE (((adm_WarrantyQuery.VehicleName) Like [Vehicle Name] & "*") AND ((adm_WarrantyQuery.ModelYear) Like "*" & [Model Year]) AND ((adm_WarrantyQuery.TIS) Between [TIS Lower Limit] And [TIS Upper Limit]))
GROUP BY adm_WarrantyQuery.VehicleName, adm_WarrantyQuery.[5DigPN]
ORDER BY Count(adm_WarrantyQuery.ClaimID) DESC;


The VehicleName is in that query only as a placeholder so that I know which Vehicle Line this query was generated for (it will be run for each individually, and we need a way to tell them apart). It will obviously be the same for each record based on the input the user gives to the prompt.

The VehicleName has no database relation to the ProductID or the Product Group (5DigPN). They are all just fields in the adm_WarrantyQuery.

When I try your query, the query looks like it is working - it actually returned a limited number of records, then calculated, then returned more, then calculated - but it ended up not responding. I am trying to make out what you are doing in the correlated subquery you posted, so I thought I would put this info out there in case you will see something that should be changed. It looks like you are totaling the claims involving a ProductID where the count of total claims appears in the sorted list of top 5 worst ProductIDs for that Vehicle...

...that last bit, where you link T.ProductID and A.ProductID... shouldn't that be the 5DigPN? I want the Top 5 ProductIDs for the Vehicle and 5DigPN, I would think... unless I'm not understanding what is happening there.

 

[rubbernilly]

OK, I modified your SQL based on what I understand of what you are doing, but obviously I do not understand enough. I am getting an error that I am trying to execute a query that does not include the specified expression 'adm_WarrantyQuery.VehicleName' as part of an aggregate function.

I do not see such an inclusion in my SQL below, but maybe in the subquery?


SELECT A.VehicleName, A.ProductID, Count(A.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS A INNER JOIN adm_T5_5DigPN_Repairs AS B ON (A.VehicleName = B.VehicleName) AND (A.[5DigPN] = B.[5DigPN])
WHERE (((A.TIS) Between [TIS Lower Limit] And [TIS Upper Limit]))
GROUP BY A.VehicleName, A.ProductID
HAVING (((Count(A.ClaimID)) In (SELECT TOP 5 Count(T.ClaimID) FROM adm_WarrantyQuery AS T
WHERE T.VehicleName = B.VehicleName AND T.[5DigPN] = B.[5DigPN] ORDER BY 1 DESC)));

 

[PHV]

Perhaps this ?
SELECT A.VehicleName, A.[5DigPN], A.ProductID, Count(A.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS A
INNER JOIN adm_T5_5DigPN_Repairs AS B ON A.[5DigPN] = B.[5DigPN] AND A.VehicleName = B.VehicleName
GROUP BY A.VehicleName, A.[5DigPN], A.ProductID
HAVING Count(A.ClaimID) IN (SELECT TOP 5 Count(T.ClaimID) FROM adm_WarrantyQuery AS T
WHERE T.VehicleName = A.VehicleName AND T.ProductID = A.ProductID AND T.[5DigPN] = A.[5DigPN] ORDER BY 1 DESC)

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

 

[rubbernilly]

This is frustrating. I think I see what you are trying to accomplish in the query. If the total of the Claims for any particular ProductID matches the total of the Claims for any of the Top 5 for that Vehicle and Product Group, then we want to include that in the output.

But the returns are off...

Your SQL returns 83 records, where some 5DigPNs have as many as 20+ ProductIDs being returned.

I modified it a bit, pulling some information from the adm_T5_5DigPN_Repairs query rather than the adm_WarrantyQuery so that I could use those fields in the correlation of the sub query. This SQL produces 82 records:

SELECT B.VehicleName, B.[5DigPN], A.ProductID, Count(A.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS A INNER JOIN adm_T5_5DigPN_Repairs AS B ON (A.VehicleName = B.VehicleName) AND (A.[5DigPN] = B.[5DigPN])
GROUP BY B.VehicleName, B.[5DigPN], A.ProductID
HAVING (((Count(A.ClaimID)) In (SELECT TOP 5 Count(T.ClaimID) FROM adm_WarrantyQuery AS T
WHERE T.VehicleName = B.VehicleName AND T.ProductID = A.ProductID AND T.[5DigPN] = B.[5DigPN] ORDER BY 1 DESC)))
ORDER BY B.VehicleName, B.[5DigPN], Count(A.ClaimID) DESC;

But it seems like I shouldn't have to link T.ProductID to A.ProductID... that I just want to generate the TOP 5 ProductIDs without regard to the ProductID of the main query. It seems like if I use the ProductID field in the correlation, then the TOP returns will be limited to that ProductID, meaning that every ProductID will actually appear in the TOP 5 list (for that ProductID). Am I understanding that right?

On the other hand, if I remove that link from the correlation, I get 0 rows returned by the query. It seems like we are close, is there any more information I can give you that would clarify things at all?

 

[rubbernilly]

Success!

I broke out the subquery to see what it was returning, then I was able to clean it up and shape it to get the results that I wanted. Now it checks for HAVING the ProductID IN the subquery result set (which is now the TOP 5 ProductIDs based on Count(ClaimID)). Here is the SQL:

SELECT B.VehicleName, A.ModelYear, B.[5DigPN], A.ProductID, Count(A.ClaimID) AS TotalRepairs
FROM adm_WarrantyQuery AS A INNER JOIN adm_T5_5DigPN_Repairs AS B ON (A.[5DigPN] = B.[5DigPN]) AND (A.VehicleName = B.VehicleName)
GROUP BY B.VehicleName, A.ModelYear, B.[5DigPN], A.ProductID
HAVING (((A.ModelYear) Like "*" & [Model Year]) AND ((A.ProductID) In (SELECT TOP 5 T.ProductID
FROM adm_WarrantyQuery AS T
WHERE (((T.VehicleName) Like [Vehicle Name] & "*") AND ((T.ModelYear) Like "*" & [Model Year]) AND ((T.[5DigPN])=.[5DigPN]) AND ((T.TIS) Between [TIS Lower Limit] And [TIS Upper Limit]))
GROUP BY T.ProductID
ORDER BY Count(T.ClaimID) DESC)))
ORDER BY B.VehicleName, B.[5DigPN], Count(A.ClaimID) DESC;

I had to have the prompts in there because they are in the original adm_T5_5DigPN_Repairs as limiters, and I need to match the result sets.

Many thanks, PHV! I couldn't have done it without you!