Solved

First Occurrence of Specific Column

Posted on 2014-09-04
8
269 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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
 
LVL 50

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 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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