Solved

First Occurrence of Specific Column

Posted on 2014-09-04
8
243 Views
Last Modified: 2014-09-06
I have a query that uses sum(Case When. The issue is that I only want to have the first row of duplicates. There are duplicates because my column 'Style' has two different outputs. How do I not have duplicate rows and select the first item of the of the 'Style' column. I have a spreadsheet of the result and highlighted in yellow the first row of the duplicates.  

SELECT DISTINCT 
                      fpo2.POName, fpo2.FactoryName, fpo1.FabricType, fpo1.Color, fpo1.Style, fpo1.ModelName, 
                      SUM(CASE WHEN od.productcode LIKE 'C-M%' THEN od.quantity ELSE 0 END) AS MenTotal, 
                      SUM(CASE WHEN od.productcode LIKE 'C-W%' THEN od.quantity ELSE 0 END) AS WomenTotal, 
                      SUM(CASE WHEN od.productcode LIKE 'C-Y%' THEN od.quantity ELSE 0 END) AS YouthTotal
FROM         FactoryPO1 AS fpo1 INNER JOIN
                      FactoryPO2 AS fpo2 ON fpo1.OrderID = fpo2.OrderID INNER JOIN
                      OrderDetails AS od ON fpo1.OrderID = od.OrderID
GROUP BY fpo1.ModelName, fpo1.Style, fpo1.Color, fpo1.FabricType, fpo2.FactoryName, fpo2.POName
ORDER BY fpo2.POName

Open in new window

SQLQuerySample09042014.xlsx
0
Comment
Question by:Southern_Gentleman
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40304959
What style is considered "First"? is it Min(Style) or Max(Style). Try this statement:
SELECT 
                      fpo2.POName, fpo2.FactoryName, fpo1.FabricType, fpo1.Color, Min(fpo1.Style) AS Style, fpo1.ModelName, 
                      SUM(CASE WHEN od.productcode LIKE 'C-M%' THEN od.quantity ELSE 0 END) AS MenTotal, 
                      SUM(CASE WHEN od.productcode LIKE 'C-W%' THEN od.quantity ELSE 0 END) AS WomenTotal, 
                      SUM(CASE WHEN od.productcode LIKE 'C-Y%' THEN od.quantity ELSE 0 END) AS YouthTotal
FROM         FactoryPO1 AS fpo1 INNER JOIN
                      FactoryPO2 AS fpo2 ON fpo1.OrderID = fpo2.OrderID INNER JOIN
                      OrderDetails AS od ON fpo1.OrderID = od.OrderID
GROUP BY fpo1.ModelName, fpo1.Color, fpo1.FabricType, fpo2.FactoryName, fpo2.POName
ORDER BY fpo2.POName

Open in new window

BTW, you do not nee DISTINCT if your GROUP BY constructed correctly
0
 

Author Comment

by:Southern_Gentleman
ID: 40305075
I tried that before I posted but it didn't work so I figure it would be something a little more difficult. Min(fpo1.Style) does not seem to work.  I removed the Distinct.
0
 
LVL 24

Expert Comment

by:chaau
ID: 40305077
Please specify what constitutes the first style. What column in the table should be used as a sort order to locate this first style for the order
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 40305421
Very often, when folks start asking for "first" or "last" or "most recent"

AND they have already tried MAX() of MIN()

THEN using ROW_NUMBER()  solves the problem.

So, it is quite possible ROW_NUMBER() will help you; BUT, I don't know what you mean by first.

Could you describe the problem, but even better could you illustrate the problem using some sample data and an expected result? thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40305637
Check if you really need the COLOR, STYLE and DESCRIPTION since are the columns than don't let you have distinct rows:
SELECT fpo2.POName, fpo2.FactoryName, fpo1.FabricType, 
       SUM(CASE WHEN od.productcode LIKE 'C-M%' THEN od.quantity ELSE 0 END) AS MenTotal, 
       SUM(CASE WHEN od.productcode LIKE 'C-W%' THEN od.quantity ELSE 0 END) AS WomenTotal, 
       SUM(CASE WHEN od.productcode LIKE 'C-Y%' THEN od.quantity ELSE 0 END) AS YouthTotal
FROM  FactoryPO1 AS fpo1 
	INNER JOIN FactoryPO2 AS fpo2 ON fpo1.OrderID = fpo2.OrderID 
	INNER JOIN OrderDetails AS od ON fpo1.OrderID = od.OrderID
GROUP BY fpo1.FabricType, fpo2.FactoryName, fpo2.POName
ORDER BY fpo2.POName

Open in new window

0
 
LVL 31

Accepted Solution

by:
awking00 earned 350 total points
ID: 40305833
>> There are duplicates because my column 'Style' has two different outputs <<
I assume one style for women and one for men. It would be relatively easy to show the style without the W or M suffix, (left(style, len(style) - 1), which would eliminate duplicates. There seems to be one exception and that is the one with the color Oange [sic] Clownfish. Is that a typo or still considered a duplicate because of the same ponumber?
0
 

Author Closing Comment

by:Southern_Gentleman
ID: 40306518
I used the LEFT LEN to remove the style but it still didn't work until I added the DISTINCT back in there so evidentially you can still use DISTINCT with GROUP BY
0
 
LVL 31

Expert Comment

by:awking00
ID: 40307517
>>evidentially you can still use DISTINCT with GROUP BY<<
You can use it, but it should be superfluous with the correct group by statement
SELECT
fpo2.POName, fpo2.FactoryName, fpo1.FabricType, fpo1.Color, left(fpo1.Style,len(fpo1.Style) - 1) as style, fpo1.ModelName,
SUM(CASE WHEN od.productcode LIKE 'C-M%' THEN od.quantity ELSE 0 END) AS MenTotal,
SUM(CASE WHEN od.productcode LIKE 'C-W%' THEN od.quantity ELSE 0 END) AS WomenTotal,
SUM(CASE WHEN od.productcode LIKE 'C-Y%' THEN od.quantity ELSE 0 END) AS YouthTotal
FROM         FactoryPO1 AS fpo1 INNER JOIN
                   FactoryPO2 AS fpo2 ON fpo1.OrderID = fpo2.OrderID INNER JOIN
                   OrderDetails AS od ON fpo1.OrderID = od.OrderID
GROUP BY fpo1.ModelName, left(fpo1.Style,len(fpo1.Style) - 1), fpo1.Color, fpo1.FabricType, fpo2.FactoryName, fpo2.POName
ORDER BY fpo2.POName
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

13 Experts available now in Live!

Get 1:1 Help Now