Improve company productivity with a Business Account.Sign Up

x
?
Solved

selecting distinct on 2 columns

Posted on 2014-01-08
9
Medium Priority
?
317 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 61

Expert Comment

by:Kevin Cross
ID: 39766121
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 41

Expert Comment

by:Sharath
ID: 39766131
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
ID: 39766216
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
Get your problem seen by more experts

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

 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39766224
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
 

Author Comment

by:iceman19330
ID: 39766288
What would that above query look like?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 39766344
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
ID: 39766383
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 41

Expert Comment

by:Sharath
ID: 39769796
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 61

Expert Comment

by:Kevin Cross
ID: 39770716
@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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

584 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