Solved

Query to find top attributes for each product

Posted on 2014-10-10
1
103 Views
Last Modified: 2015-01-08
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
Comment
Question by:bobinorlando
1 Comment
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40374405
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Graphics 2 27
SSRS  - Dropdown with Null 3 24
List<PaisEntity> - show some 1 24
sql how to count case when 4 14
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question