Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

First Occurrence of Specific Column

Posted on 2014-09-04
8
Medium Priority
?
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 25

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 25

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 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
 
LVL 52

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 32

Accepted Solution

by:
awking00 earned 1400 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 32

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

721 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