• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

Query to find top attributes for each product

Experts,
I have a product database.  It contains hundreds of thousands of transaction history records for thousands of products over time. There 20-30 columns that store various attributes for each record e.g.  productid, productname etc. and also columns for e.g. product size and various other product features, etc. as well as columns for customer data pertaining to the purchase.

What I would like to do is find the TOP n number of some given product feature for each product.
e.g. top 5 sizes for each product or top 5 zipcodes of customers who purchased the product.

Ideally I would like the result set to look like this:
e.g. Top 5 zipcodes per product - each zipcode in a column:

Product ID   Zipcode  Zipcode  Zipcode Zipcode Zipcode

or
Top 5 zipcodes all in the same column:

ProductID Zipcode, Zipcode, Zipcode, Zipcode

What is the best approach to this sort of problem?

Thanks in advance.
0
bobinorlando
Asked:
bobinorlando
1 Solution
 
PortletPaulfreelancerCommented:
From a SQL perspective the best approach is neither of those output options.

But if I had to choose from only those 2 options I would go with the second; because you can "union all" the individual outputs

select TOP (5) productid, comma_list, 'zipcodes' as [what]
from ...
group by
order by ... DESC

UNION ALL

select TOP (5) productid, comma_list, 'style/colour' as [what]
from ...
group by
order by ... DESC

...

If doing a temp table or similar, I would just insert using a table structure like this

productid | topnvalue (varchar) | what (varchar)

then you can fiddle with the output of this to meet the need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now