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
Solved

First Occurrence of Specific Column

Posted on 2014-09-04
8
255 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 48

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

791 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