Complex SELECT query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex SELECT query

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-04 : 15:03:05

I think I have something wrong with me, but I can't figure out for the life of me how to generate the results I want. I ultimately want a record set of related products within my database. I have a table that has all of the criteria that I want to use to relate my products together. Essentially I am going to use a field called "Brand", "Color", "Gender", and "Total Sales" to see how items are related to eachother.

here's what I'm after. I want the top 10 items (sorted descending by Total Sales) for each item. So, the data should look like this:

Style1, Brand, Color, 10, RelatedStyle1
Style1, Brand, Color, 8, RelatedStyle2
Style1, Brand, Color, 6, RelatedStyle3
Style1, Brand, Color, 5, RelatedStyle4
Style1, Brand, Color, 4, RelatedStyle5
Style1, Brand, Color, 4, RelatedStyle6
Style1, Brand, Color, 3, RelatedStyle7
Style1, Brand, Color, 2, RelatedStyle8
Style1, Brand, Color, 1, RelatedStyle9
Style1, Brand, Color, 1, RelatedStyle10

Style2, Brand, Color, 18, RelatedStyle1
Style2, Brand, Color, 12, RelatedStyle2
Style2, Brand, Color, 10, RelatedStyle3
etc, etc.....

Any idea?

Thanks in advance.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-04 : 15:10:40

I don't quite follow you but I think you are looking at a row-number functions

Check out ROW_NUMBER() in BOL

The syntax would be something like

ROW_NUMBER() OVER ( PARTITION BY [Brand], [Color], [Gender] ORDER BY [Total Sales] DESC ) AS rank

and take where the rank is <= 10

But I'm not 100% sure I understand what you are asking for

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-04 : 16:33:51

Thank you! This is almost exactly what I need. I took what you gave me and modified a little bit. I still have one more piece to hook up to this and then I should have exactly what I need.

Here's where I am:

SELECT Style, PrimaryColor, Brand, TotalSales, Gender, ROW_NUMBER() OVER ( PARTITION BY [Brand], [PrimaryColor], [Gender] ORDER BY [TotalSales] DESC ) AS rank
FROM PPB_RelatedData

This is giving me the result information combined and sorted exactly as I need it, what I need at this point is this data iterated over all the records within the same table. For example, the SQL above lists all skus that fall in a like Color, Brand, Gender combination. So for every sku within that combination I want to indicate that these are related items to the given SKU.

So I'd end up with something like this:

Style, RelatedStyle, Color, Brand, Gender, TotalSales, Rank

The bolded items are being generated in the SQL above. Just need to connect up the resulting "Related set" for every primary key within the table.

Having a hard time explaining exactly what I need.

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-04 : 19:07:51

I see a lot of people reading this topic but nobody replying. I'm wondering if I'm not clear on what I need.

The RowNumber() and Over() portion of this statement are providing me with the sets of records that I need. Ultimately I need to group like items together and rank them by TotalSales. But then I need to associate those created "groups" with each record in my table.

So for example, if I have a group that has a Black color, is for Brand X, and fits gender Women, I get 10 results with the one that sells the best on top. Now for EACH RECORD that matches Black colors, Brand X, gender Women, I need to show the associated "group" of records created in the RowNumber() and Over() portion of the SQL statement posted. So I'd get something like this:

From Group1

Style1, Group1, RelatedStyle1
Style1, Group1, RelatedStyle2
Style1, Group1, RelatedStyle3
Style1, Group1, RelatedStyle4
Style1, Group1, RelatedStyle5
Style1, Group1, RelatedStyle6
Style1, Group1, RelatedStyle7
Style1, Group1, RelatedStyle8
Style1, Group1, RelatedStyle9
Style1, Group1, RelatedStyle10
Style2, Group1, RelatedStyle1
Style2, Group1, RelatedStyle2
Style2, Group1, RelatedStyle3
Style2, Group1, RelatedStyle4
Style2, Group1, RelatedStyle5
Style2, Group1, RelatedStyle6
Style2, Group1, RelatedStyle7
Style2, Group1, RelatedStyle8
Style2, Group1, RelatedStyle9
Style2, Group1, RelatedStyle10
Style3, Group1, RelatedStyle1
etc.

Does this make more sense?

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-07 : 15:33:07

Still appears that there are lots of people reading this but nobody answering. I'm thinking that this may need to be some sort of 2 step process to get exactly where I want to get. If there are any other methods anyone can suggest, I'm completely open at this point.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-07 : 15:41:30

Sorry -- I've been busy and I thought someone else would help.

I'm not 100% sure of your output


Style1, Group1, RelatedStyle1
Style1, Group1, RelatedStyle2
Style1, Group1, RelatedStyle3
Style1, Group1, RelatedStyle4
Style1, Group1, RelatedStyle5
Style1, Group1, RelatedStyle6
Style1, Group1, RelatedStyle7
....
....

Can you please post some real sample data in the form of a table defintion and insert statements? A temp table or table variable would be absolutely fine.

Then based on that fake data, the required output for that fake data? I don't see why the ROW_NUMBER FUNCTION isn't working for you.

best of luck -- I'll keep checking this thread tomorrow.

Charlie

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-07 : 16:34:20

Thanks for the reply. Sorry to nudge, I suspected I haven't been clear enough on what I need. Here is the SQL statement that is getting me very very close to what I need:

SELECT Parent_SKU AS Related_SKU, PrimaryColor, Brand, TotalSales, TargetAudience, ROW_NUMBER() OVER ( PARTITION BY [Brand], [PrimaryColor], [TargetAudience] ORDER BY [TotalSales] DESC ) AS Rank
FROM PPB_RelatedData

The results of that query are too long to post here, but I'll put a few of them:


Parent_SKU	PrimaryColor	Brand	TotalSales	TargetAudience	Rank
BG8265		black		Brand1	2		women		1
BG6284		black		Brand1	1		women		2
BG6236		black		Brand1	0		women		3
BG8255		black		Brand1	0		women		4
BG6394		black		Brand1	0		women		5
BG6400		black		Brand1	0		women		6
BG8230		black		Brand1	0		women		7
BG6298		black		Brand1	0		women		8
BG6299		black		Brand1	0		women		9
BG6301		black		Brand1	0		women		10
BG6349		black		Brand1	0		women		11
BG6365		black		Brand1	0		women		12
BG6389		black		Brand1	0		women		13
BG6344		blue		Brand1	0		women		1
BG8222		gold		Brand1	0		women		1
BG6341		grey		Brand1	0		women		1
BG6369		mother-of-pearl	Brand1	0		women		1
BG6336		red		Brand1	0		women		1
BG8238		silver		Brand1	0		women		1
BG6291		silver		Brand1	1		women		1
BG6292		silver		Brand1	0		women		2
BG6274		silver		Brand1	0		women		3
BG8229		silver		Brand1	0		women		4
BG8237		silver		Brand1	0		women		5
BG6323		silver		Brand1	0		women		6
BG6329		silver		Brand1	0		women		7
BG6330		white		Brand1	0		women		1
BG6382		white		Brand1	0		women		2
BG6384		white		Brand1	0		women		3
BG6391		white		Brand1	0		women		4
BG6367		white		Brand1	0		women		5
BG6350		white		Brand1	0		women		6
BG6356		white		Brand1	0		women		7
BG8209		white		Brand1	0		women		8
BG6395		white		Brand1	0		women		9
BG6398		white		Brand1	0		women		10
BG8248		white		Brand1	0		women		11
BG8256		white		Brand1	0		women		12
BG8267		white		Brand1	0		women		13
FS81324		black		Brand2	2		men		1
FS84898		black		Brand2	2		men		2
FS84873		black		Brand2	1		men		3
FS84841		black		Brand2	1		men		4
FS81285		black		Brand2	0		men		5
FS80936		black		Brand2	0		men		6
FS81202		black		Brand2	0		men		7
FS81241		black		Brand2	0		men		8
FS84872		grey		Brand2	0		men		1
FS84899		grey		Brand2	0		men		2
FS84897		silver	Brand2	3		men		1
FS81284		silver	Brand2	2		men		2
FS84913		silver	Brand2	1		men		3
FS81242		silver	Brand2	1		men		4
FS84877		silver	Brand2	0		men		5
FS84840		silver	Brand2	0		men		6
FS84843		silver	Brand2	0		women		1
FS81323		white		Brand2	3		men		1
FS84878		white		Brand2	1		men		2
FS84871		white		Brand2	0		men		3
FS75449		white		Brand2	0		men		4
FS81244		yellow		Brand2	1		men		1
FS80937		yellow		Brand2	0		men		2

You can see that this statement is grouping together items based on if they share characteristics. So, anything that has "black" as PrimaryColor, is "Brand1" in Brand, and has "women" as Target audience is then grouped and ranked in order of total sales column. Lets say that every items linked by these shared characteristics comprise a group, and each group is made up of 1 or more items and then the groups are consecutively numbered. So, in this instance, anything that is black, meant for women, and is "Brand1" forms group1. From the same results, anything that is "blue", "brand1", "women" forms group2 (there is only 1 item in this group on these results), etc.

What I need as a result would look like this:


Parent_SKU	Related_SKU	PrimaryColor	Brand	TotalSales	TargetAudience	Rank

BG8265		BG8265		black		Brand1	2		women		1
BG8265		BG6284		black		Brand1	1		women		2
BG8265		BG6236		black		Brand1	0		women		3
BG8265		BG8255		black		Brand1	0		women		4
BG8265		BG6394		black		Brand1	0		women		5
BG8265		BG6400		black		Brand1	0		women		6
BG8265		BG8230		black		Brand1	0		women		7
BG8265		BG6298		black		Brand1	0		women		8
BG8265		BG6299		black		Brand1	0		women		9
BG8265		BG6301		black		Brand1	0		women		10
BG8265		BG6349		black		Brand1	0		women		11
BG8265		BG6365		black		Brand1	0		women		12
BG8265		BG6389		black		Brand1	0		women		13

BG6284		BG8265		black		Brand1	2		women		1
BG6284		BG6284		black		Brand1	1		women		2
BG6284		BG6236		black		Brand1	0		women		3
BG6284		BG8255		black		Brand1	0		women		4
BG6284		BG6394		black		Brand1	0		women		5
BG6284		BG6400		black		Brand1	0		women		6
BG6284		BG8230		black		Brand1	0		women		7
BG6284		BG6298		black		Brand1	0		women		8
BG6284		BG6299		black		Brand1	0		women		9
BG6284		BG6301		black		Brand1	0		women		10
BG6284		BG6349		black		Brand1	0		women		11
BG6284		BG6365		black		Brand1	0		women		12
BG6284		BG6389		black		Brand1	0		women		13

BG6236		BG8265		black		Brand1	2		women		1
BG6236		BG6284		black		Brand1	1		women		2
BG6236		BG6236		black		Brand1	0		women		3
BG6236		BG8255		black		Brand1	0		women		4
BG6236		BG6394		black		Brand1	0		women		5
BG6236		BG6400		black		Brand1	0		women		6
BG6236		BG8230		black		Brand1	0		women		7
BG6236		BG6298		black		Brand1	0		women		8
BG6236		BG6299		black		Brand1	0		women		9
BG6236		BG6301		black		Brand1	0		women		10
BG6236		BG6349		black		Brand1	0		women		11
BG6236		BG6365		black		Brand1	0		women		12
BG6236		BG6389		black		Brand1	0		women		13

BG8255		BG8265		black		Brand1	2		women		1
BG8255		BG6284		black		Brand1	1		women		2
BG8255		BG6236		black		Brand1	0		women		3
BG8255		BG8255		black		Brand1	0		women		4
BG8255		BG6394		black		Brand1	0		women		5
BG8255		BG6400		black		Brand1	0		women		6
BG8255		BG8230		black		Brand1	0		women		7
BG8255		BG6298		black		Brand1	0		women		8
BG8255		BG6299		black		Brand1	0		women		9
BG8255		BG6301		black		Brand1	0		women		10
BG8255		BG6349		black		Brand1	0		women		11
BG8255		BG6365		black		Brand1	0		women		12
BG8255		BG6389		black		Brand1	0		women		13

BG6394		BG8265		black		Brand1	2		women		1
BG6394		BG6284		black		Brand1	1		women		2
BG6394		BG6236		black		Brand1	0		women		3
BG6394		BG8255		black		Brand1	0		women		4
BG6394		BG6394		black		Brand1	0		women		5
BG6394		BG6400		black		Brand1	0		women		6
BG6394		BG8230		black		Brand1	0		women		7
BG6394		BG6298		black		Brand1	0		women		8
BG6394		BG6299		black		Brand1	0		women		9
BG6394		BG6301		black		Brand1	0		women		10
BG6394		BG6349		black		Brand1	0		women		11
BG6394		BG6365		black		Brand1	0		women		12
BG6394		BG6389		black		Brand1	0		women		13

BG6400		BG8265		black		Brand1	2		women		1
BG6400		BG6284		black		Brand1	1		women		2
BG6400		BG6236		black		Brand1	0		women		3
BG6400		BG8255		black		Brand1	0		women		4
BG6400		BG6394		black		Brand1	0		women		5
BG6400		BG6400		black		Brand1	0		women		6
BG6400		BG8230		black		Brand1	0		women		7
BG6400		BG6298		black		Brand1	0		women		8
BG6400		BG6299		black		Brand1	0		women		9
BG6400		BG6301		black		Brand1	0		women		10
BG6400		BG6349		black		Brand1	0		women		11
BG6400		BG6365		black		Brand1	0		women		12
BG6400		BG6389		black		Brand1	0		women		13

etc.....

Is this more clear where I'm going with this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources