Solved

First Occurrence of Specific Column

Posted on 2014-09-04
8
246 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 46

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

28 Experts available now in Live!

Get 1:1 Help Now