Solved

selecting distinct on 2 columns

Posted on 2014-01-08
9
302 Views
Last Modified: 2014-02-05
The issue is that on the site we list the sku with options for the queen (we dont have the king, full or twin info) and then I need to show the additional dimensions for King, Full and Twin so I pulled an excel sheet with all the manufactures information into a single table (16k rows) and am trying to filter the information (its down to 11k on a where statement selecting for sizes).  

What I would like to do is for each of the queen skus on the website find the corresponding dimension as well if there is a dimension for King, Twin and Full based on the parent sku field.

So output would look like:
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10

Open in new window


Additionally there would need to filter out these
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10
2-ABC-10-20-Conventional,Queen,85,26,23.345,2-ABC-10 --filter
2-ABC-20-30-Conventional,King,88,27,23.345,2-ABC-10 --filter
2-ABC-21-30-Conventional,Cali King,88,27,23.345,2-ABC-10 --filter
2-ABC-22-37-Conventional,37" King,88,27,23.345,2-ABC-10 --filter
2-ABC-23-53-Conventional,Full,88,27,23.345,2-ABC-10 --filter
2-ABC-12-30-Conventional,Twin,88,27,23.345,2-ABC-10 --filter

Open in new window

So here is my starting query
SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU` 
FROM TABLE2
WHERE Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin'
AND `SKU with Options` not like '%Conventional'

Open in new window


My query brings in
2-ABC-10-20,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30,King,88,27,23.345,2-ABC-10
2-ABC-21-30,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53,Full,88,27,23.345,2-ABC-10
2-ABC-12-30,Twin,88,27,23.345,2-ABC-10
2-ABC-10-20-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-30-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-30-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-37-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-53-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-30-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-11,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11,King,88,27,23.345,2-ABC-10
2-ABC-21-11,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11,Full,88,27,23.345,2-ABC-10
2-ABC-12-11,Twin,88,27,23.345,2-ABC-10
2-ABC-10-11-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-11-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-11-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-15,Queen,85,26,23.345,2-ABC-10
2-ABC-20-15,King,88,27,23.345,2-ABC-10
2-ABC-21-15,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-15,37" King,88,27,23.345,2-ABC-10
2-ABC-23-15,Full,88,27,23.345,2-ABC-10
2-ABC-12-15,Twin,88,27,23.345,2-ABC-10
2-ABC-10-15-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-15-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-15-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-15-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-15-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-15-Conventional,Twin,88,27,23.345,2-ABC-10
2-ABC-10-21,Queen,85,26,23.345,2-ABC-10
2-ABC-20-21,King,88,27,23.345,2-ABC-10
2-ABC-21-21,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-21,37" King,88,27,23.345,2-ABC-10
2-ABC-23-21,Full,88,27,23.345,2-ABC-10
2-ABC-12-21,Twin,88,27,23.345,2-ABC-10
2-ABC-10-21-Conventional,Queen,85,26,23.345,2-ABC-10
2-ABC-20-21-Conventional,King,88,27,23.345,2-ABC-10
2-ABC-21-21-Conventional,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-21-Conventional,37" King,88,27,23.345,2-ABC-10
2-ABC-23-21-Conventional,Full,88,27,23.345,2-ABC-10
2-ABC-12-21-Conventional,Twin,88,27,23.345,2-ABC-10

Open in new window


There can be 50+ different skus with options for each parent sku due to size, finish and wood type, hence why I am having an issue just getting the single king, queen, full, twin plus the occasional cali king or 37" king/queen odd size.
0
Comment
Question by:iceman19330
  • 4
  • 3
  • 2
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
First, try adjusting the WHERE to fix what I believe is your intent.

SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU`
FROM TABLE2
WHERE Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin'
AND `SKU with Options` not like '%Conventional'


The highlighted syntax finds king, queen, or full size beds as well as twin size beds that do not contain the word Conventional at the end of SKU.  I suspect you want the below.

SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Parent SKU`
FROM TABLE2
WHERE `SKU with Options` not like '%Conventional'
AND (Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin')


Parentheses makes a difference here.

Let me know if that looks better.  My next thought, is you could use `SKU with Options` like a specific pattern that both excludes "conventional" and other options you may not want.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
The information you provided is not sufficient to provide any suggestion (at least to me). Explain with some simple examples, what data you have in table, what are you expecting any why (in plain English)?
0
 

Author Comment

by:iceman19330
Comment Utility
Kevin that did remove those pesky "Conventional" skus.  :)

This is what I am left with -- this is a small set for example purposes
1-ABC-01-23	King	82	86	14.25	1-ABC-02
1-ABC-01-33	King	82	86	14.25	1-ABC-02
1-ABC-01-03	King	82	86	14.25	1-ABC-02
1-ABC-01-43	King	82	86	14.25	1-ABC-02
1-ABC-01-53	King	82	86	14.25	1-ABC-02
1-ABC-01-13	King	82	86	14.25	1-ABC-02
1-ABC-01-81	King	82	86	14.25	1-ABC-02
1-ABC-01-71	King	82	86	14.25	1-ABC-02
1-ABC-01-11	King	82	86	14.25	1-ABC-02
1-ABC-01-21	King	82	86	14.25	1-ABC-02
1-ABC-01-61	King	82	86	14.25	1-ABC-02
1-ABC-01-01	King	82	86	14.25	1-ABC-02
1-ABC-01-41	King	82	86	14.25	1-ABC-02
1-ABC-01-51	King	82	86	14.25	1-ABC-02
1-ABC-01-14	King	82	86	14.25	1-ABC-02
1-ABC-02-23	Queen	66	86	14.25	1-ABC-02
1-ABC-02-33	Queen	66	86	14.25	1-ABC-02
1-ABC-02-03	Queen	66	86	14.25	1-ABC-02
1-ABC-02-43	Queen	66	86	14.25	1-ABC-02
1-ABC-02-53	Queen	66	86	14.25	1-ABC-02
1-ABC-02-13	Queen	66	86	14.25	1-ABC-02
1-ABC-02-81	Queen	66	86	14.25	1-ABC-02
1-ABC-02-71	Queen	66	86	14.25	1-ABC-02
1-ABC-02-11	Queen	66	86	14.25	1-ABC-02
1-ABC-02-21	Queen	66	86	14.25	1-ABC-02
1-ABC-02-61	Queen	66	86	14.25	1-ABC-02
1-ABC-02-01	Queen	66	86	14.25	1-ABC-02
1-ABC-02-41	Queen	66	86	14.25	1-ABC-02
1-ABC-02-51	Queen	66	86	14.25	1-ABC-02
1-ABC-02-14	Queen	66	86	14.25	1-ABC-02
1-ABC-05-23	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-33	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-03	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-43	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-53	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-13	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-81	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-71	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-11	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-21	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-61	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-01	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-41	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-51	Cal King	78	90	14.25	1-ABC-02
1-ABC-05-14	Cal King	78	90	14.25	1-ABC-02
1-ABC-11-23	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-33	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-03	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-43	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-53	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-13	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-81	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-71	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-11	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-21	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-61	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-01	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-41	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-51	King	82	87.5	40 or 44	1-ABC-12
1-ABC-11-14	King	82	87.5	40 or 44	1-ABC-12
1-ABC-12-23	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-33	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-03	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-43	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-53	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-13	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-81	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-71	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-11	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-21	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-61	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-01	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-41	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-51	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-12-14	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-15-23	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-33	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-03	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-43	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-53	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-13	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-81	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-71	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-11	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-21	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-61	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-01	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-41	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-51	Cal King	78	91.5	40 or 44	1-ABC-12
1-ABC-15-14	Cal King	78	91.5	40 or 44	1-ABC-12

Open in new window


And this would be what I was looking for
1-ABC-01-23	King	82	86	14.25	1-ABC-02
1-ABC-02-23	Queen	66	86	14.25	1-ABC-02
1-ABC-05-23	Cal King	78	90	14.25	1-ABC-02
1-ABC-11-23	King	82	87.5	40 or 44	1-ABC-12
1-ABC-12-14	Queen	66	87.5	40 or 44	1-ABC-12
1-ABC-15-23	Cal King	78	91.5	40 or 44	1-ABC-12

Open in new window


I had thought about using an IN statement to load the 900+ skus from the product database which is only the Queen size (ex: 1-ABC-02-23) and then using that to look up the parent sku (1-ABC-02) to get the other sizes for that parent sku (1-ABC-02), however I am still left with the wondrous issue of all the finish options available for the 1 size -- 1-ABC-02 has 12 for King alone.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Yes, it is difficult to discern what you are after, which is why I started with the obvious (at least perceived) logic error.

Although, in re-reading, the following would make sense.

- select Queen bed '2-ABC-10-11', which has parent SKU '2-ABC-10'
- find every SKU that also has parent SKU '2-ABC-10'
- filter to only SKUs with same ending option as the queen bed
2-ABC-10-11,Queen,85,26,23.345,2-ABC-10
2-ABC-20-11,King,88,27,23.345,2-ABC-10
2-ABC-21-11,Cali King,88,27,23.345,2-ABC-10
2-ABC-22-11,37" King,88,27,23.345,2-ABC-10
2-ABC-23-11,Full,88,27,23.345,2-ABC-10
2-ABC-12-11,Twin,88,27,23.345,2-ABC-10

Open in new window


To get the above, you match on both parent SKU and the following substring:
SUBSTRING_INDEX(`SKU with Options`, '-', -1)

If this is not the desired result, then it is helpful to have a more plain breakdown of sample data, desired business logic (in English), and expected output as Sharath said.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:iceman19330
Comment Utility
What would that above query look like?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
How are you currently passing it the SKU relating to the Queen selected by user?  I will start with that query and show you an example from there once I get back online.
0
 

Author Comment

by:iceman19330
Comment Utility
SELECT `SKU with Options`,Size,`Product Width`,`Product Length`,`Product Height`,`Grouping by fixture (*)`
FROM TABLE2
WHERE `SKU with Options` IN ('2-ABC-10-01','2-ABC-40-03','2-ABC-50-03','2-ABC-60-03','2-ABC-30-01','2-ABC-40-01','2-ABC-50-01','2-ABC-60-01','2-ABC-35-01','1-ABC-22-01','1-ABC-12-01')
AND `SKU with Options` not like '%Water Borne'
AND (Size like '%King' OR Size like '%Queen' OR Size like 'Full' OR Size like 'Twin')
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
On what basis, you are selecting few records when there are lot of matching reocrds?
for ex, the below Queen is in 1-ABC-12 but not selected in the final set.
1-ABC-12-51      Queen      66      87.5      40 or 44      1-ABC-12
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
@iceman19330:

I guess I was expecting the application to pass in the ID of a specific queen bed, then you use the results of some initial query (e.g., to get parent SKU) as the filter for the query you are working on here.  Maybe I am misunderstanding the usage.

Given my understanding, I would think your query would look something like the below.
SELECT ab.`SKU with Options`, ab.Size, ab.`Product Width`
     , ab.`Product Length`, ab.`Product Height`, ab.`Parent SKU` 
FROM TABLE2 ab
JOIN (
    SELECT SUBSTRING_INDEX(`SKU with Options`, '-', -1) `Option`
         , `Parent SKU`
    FROM TABLE2
    WHERE `SKU with Options` = 'your id from initial selection'
) qb ON qb.`Parent SKU` = ab.`Parent SKU`
    AND qb.`Option` = SUBSTRING_INDEX(ab.`SKU with Options`, '-', -1)
WHERE ab.`SKU with Options` not like '%Conventional'
AND (
    ab.Size like '%King' 
    OR ab.Size like '%Queen' 
    OR ab.Size like 'Full' 
    OR ab.Size like 'Twin'
);

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now