locked
write query with all possible permutations RRS feed

  • Question

  • I need help writing a query which retrieves all permutations of a combination of the values: instrument_id, scoring_id, style_id.

    I have 3 tables: TopSellerInstrumentsLastWeek,  TopSellerScoringLastWeek, TopSellerStyleLastWeek

    I have come up with 7 possible permutations, I am using the column name:

        instrument_id
        instrument_id, scoring_id
        instrument_id, style_id
        scoring_id
        style_id, scoring_id
        style_id
        instrument_id, style_id, scoring_id

    I need to be able to find the Top 5 sellers using all the permutations listed above. Regardless of the combination, there should always be 5 results. This should be a stored procedure that takes in all possible combinations.

    Any help I can get, would mean the world, since I have been struggling understanding how to write a query like this one. I hope I am explaining my problem clearly, but if I am not, please let me know and I can try to provide more details to this. 

    Thank you very much.

    P.S.

    how can I include data to populate the 3 tables, and also the create table scripts?

    • Edited by rafuria Tuesday, September 6, 2016 9:54 PM added text
    Tuesday, September 6, 2016 9:53 PM

Answers

  • Something like this should do the trick...

    IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL 
    DROP TABLE #Temp;
    
    CREATE TABLE #Temp (
    	SomeValue VARCHAR(20) NOT NULL
    	);
    
    INSERT #Temp (SomeValue) VALUES ('instrument_id'), ('scoring_id'), ('style_id');
    
    WITH 
    	cte_Base AS (
    		SELECT
    			SomeValue = CAST(t.SomeValue AS VARCHAR(8000))
    		FROM
    			#Temp t
    		UNION ALL 
    		SELECT 
    			SomeValue = CAST(CONCAT(t.SomeValue, ', ', b.SomeValue) AS VARCHAR(8000))
    		FROM
    			#Temp t
    			JOIN cte_Base b
    				ON b.SomeValue NOT LIKE '%' + t.SomeValue + '%'
    				AND b.SomeValue > t.SomeValue
    		)
    SELECT
    	b.SomeValue
    FROM
    	cte_Base b;

    The Results...

    SomeValue
    ----------
    instrument_id
    scoring_id
    style_id
    instrument_id, style_id
    scoring_id, style_id
    instrument_id, scoring_id, style_id
    instrument_id, scoring_id

    HTH,

    Jason


    Jason Long

    Wednesday, September 7, 2016 1:20 AM

All replies

  • I take it you are getting errors when trying to include code blocks and that's why you ask how to do it? It would appear any new signups are being blocked from doing such as someone else had the same problem, hopefully someone on the admin team will be taking note and fix.

    As for your question, unfortunately, it's going to be hard to help without seeing an example. What determines your top 5?

    Tuesday, September 6, 2016 11:09 PM
  • To post code, follow the instructions in https://social.msdn.microsoft.com/Forums/en-US/8e38b637-2a1e-4681-8937-337372b4d0fe/posting-tips-code-images-hyperlinks-details?forum=transactsql.  When you get to the point in your question where you want to insert the code just click on the insert code block as shown in the above like.  Cut and paste (or type) your code into the left box of the window you get, you can click on Preview to see in the way the code will look in your post.  When you are done, click on the Insert button and that will insert the code into your question.

    Including the tables and sample data would be very helpful so that we understand your question.  Also, please include the result you want from that sample data.

    Welcome to the forum.

    Tom

    Tuesday, September 6, 2016 11:47 PM
  • There are two approaches that I might consider, the first is to use a single in memory table and then populate it with a query for each table, finally select the result from the memory table to get the top 5 across all 3 input tables.

    The second approach is to use a Common Table Expression (CTE)

    With TopSellerInstrumentsLastWeek_CTE(a,b,c) AS (

     // do stuff to get top 5

    ), TopSellerScoringLastWeek_CTE(a,b,c) AS (

     // do similar stuff etc

    ),

    SELECT whateveryou need from the output of each.

    Also, if you have control over the data tables, have you considered putting them in third normal form?   You have seller, instrument, scoring, style and period as candidates.

     
    Wednesday, September 7, 2016 12:22 AM
  • Something like this should do the trick...

    IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL 
    DROP TABLE #Temp;
    
    CREATE TABLE #Temp (
    	SomeValue VARCHAR(20) NOT NULL
    	);
    
    INSERT #Temp (SomeValue) VALUES ('instrument_id'), ('scoring_id'), ('style_id');
    
    WITH 
    	cte_Base AS (
    		SELECT
    			SomeValue = CAST(t.SomeValue AS VARCHAR(8000))
    		FROM
    			#Temp t
    		UNION ALL 
    		SELECT 
    			SomeValue = CAST(CONCAT(t.SomeValue, ', ', b.SomeValue) AS VARCHAR(8000))
    		FROM
    			#Temp t
    			JOIN cte_Base b
    				ON b.SomeValue NOT LIKE '%' + t.SomeValue + '%'
    				AND b.SomeValue > t.SomeValue
    		)
    SELECT
    	b.SomeValue
    FROM
    	cte_Base b;

    The Results...

    SomeValue
    ----------
    instrument_id
    scoring_id
    style_id
    instrument_id, style_id
    scoring_id, style_id
    instrument_id, scoring_id, style_id
    instrument_id, scoring_id

    HTH,

    Jason


    Jason Long

    Wednesday, September 7, 2016 1:20 AM
  • You may want to consider not using a temp table and using a memory table

    DECLARE @mytable TABLE....

    The data set is likely to be small.

    Wednesday, September 7, 2016 2:08 AM
  • I am having problems adding the code. I will try in a little bit more as I get to work. 
    Thank you!

    Wednesday, September 7, 2016 11:12 AM