Problems with distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems with distinct

Author

Topic

Hdiva
Starting Member

10 Posts

Posted - 2006-06-26 : 14:31:04

I cannot figure out a way to get the following query to work. I need to run this query and only bring back those where the Item is distinct.

Select PlantID, VendorName, PurchaseDoc, POrg, MaterialID, MatDesc, ConsignItms,
Item, ScheduleID, FirstFillRate, ScheduledQty, AppliedQty, OrderDate,
PostingDate, StatDlvDate, CurrentPrice, HistAvgPrice, PcsEarlyPer, PcsOnTimePer,
PcsLatePer, PlannedDlvTime, ActualLeadTime, ReversedQty, GrLotsRejected,
StockRejected, StandardExtPrice, CurrentExtPrice, Variance, PlannedPrice,
undlvry_tlrnc_lmt, ovrdlvry_tlrnc_lmt
FROM POLines
Where POLines.VendorID = '2017558'
And POLines.PlantID = '2030'
And (POLines.PostingDate Between '4/1/2006' And '4/30/2006')
And (deliveryDays between 1 and 5)

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-26 : 14:32:16

Use SELECT DISTINCT????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hdiva
Starting Member

10 Posts

Posted - 2006-06-26 : 15:22:06

I have tried using distinct in the select several ways to get the data back that I require. I need all of the fields I had selected so I tried this way and it didn't work.

Select distinct Item, *
FROM POLines
Where POLines.VendorID = '2017558'
And POLines.PlantID = '2030'
And (POLines.PostingDate Between '4/1/2006' And '4/30/2006')
And (deliveryDays between 1 and 5)

If I use the distinct this way, it brings the item number back correctly but I need all the additional data.

Select distinct Item
FROM POLines
Where POLines.VendorID = '2017558'
And POLines.PlantID = '2030'
And (POLines.PostingDate Between '4/1/2006' And '4/30/2006')
And (deliveryDays between 1 and 5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 15:46:07

When using *, you automatically select EVERY field in EVERY table involved.

Select distinct PlantID, VendorName, PurchaseDoc, POrg, MaterialID, MatDesc, ConsignItms,
Item, ScheduleID, FirstFillRate, ScheduledQty, AppliedQty, OrderDate, 
PostingDate, StatDlvDate, CurrentPrice, HistAvgPrice, PcsEarlyPer, PcsOnTimePer,
PcsLatePer, PlannedDlvTime, ActualLeadTime, ReversedQty, GrLotsRejected, 
StockRejected, StandardExtPrice, CurrentExtPrice, Variance, PlannedPrice,
undlvry_tlrnc_lmt, ovrdlvry_tlrnc_lmt 
FROM POLines 
Where POLines.VendorID = '2017558' 
And POLines.PlantID = '2030' 
And (POLines.PostingDate Between '4/1/2006' And '4/30/2006')
And (deliveryDays between 1 and 5)

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-26 : 15:50:57

My guess is that he's saying that he only wants some of the columns to be unique...and really doesn't care about the others...

Which columns have to be unique?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hdiva
Starting Member

10 Posts

Posted - 2006-06-26 : 15:52:39

Sorry, that doesn't work. I know, it frustrates me to. I know there has got to be a simple fix but I can't seem to find the combination.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-26 : 15:56:19

Maybe if you posted some sample data here and an example of the output BASED on the provided samples.

Peter Larsson
Helsingborg, Sweden

Hdiva
Starting Member

10 Posts

Posted - 2006-06-26 : 16:41:29

I want the Item to be distinct only. I am attaching code without all the fields due to number of columns. Here is what the query returns.

Plant Vendor ProdOrder Item
2030 EARLE M JORGE NA01 100162312 K 1 1
2030 EARLE M JORGE NA01 100162312 K 1 1
2030 EARLE M JORGE NA01 100162310 K 8 1
2030 EARLE M JORGE NA01 100161259 NULL 5 1

This is what the query should return if the distinct worked properly.
Plant Vendor ProdOrder Item
2030 EARLE M JORGE NA01 100162312 K 1 1
2030 EARLE M JORGE NA01 100162310 K 8 1
2030 EARLE M JORGE NA01 100161259 NULL 5 1

As you can see in the first query, Item 1 of 100162312 is duplicated because of other factors in the data.

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-26 : 16:51:35

Well with your sample data supplied, SELECT DISTINCT on those columns would give you those reults...I think You want to use a SCALAR Function for the "non-unique" columns, and use a GROUP BY...BUT the data would be sort of meaningless, so why select it in the first place...

Also, I don't even see PROD_ORDER in your orignial post...so what's up yo?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hdiva
Starting Member

10 Posts

Posted - 2006-06-26 : 17:26:17

Brett, I am so sorry, that was a type-o on me. I mess with purchase documents and productions orders a lot and I just mistyped the column header. It is purchasedoc instead of ProdOrder. You are right on not selecting all the extras, let me give it a go another way. Thanks so much for being my sounding board.

Hdiva
Starting Member

10 Posts

Posted - 2006-06-27 : 15:32:38

After trying many way to fix this problem now another problem has risen. To get the distinct records and still bring in all the required fields required summing some fields. Now, AppliedQty will not sum inside this query but I can sum it without the other fields and it works.

Here is my query:

Select PlantID, VendorName, PurchaseDoc, POrg, MaterialID, MatDesc, ConsignItms,
Item, ScheduleID, FirstFillRate, ScheduledQty, sum(AppliedQty) as AppliedQty, OrderDate,
PostingDate, StatDlvDate, CurrentPrice, HistAvgPrice, PcsEarlyPer, PcsOnTimePer,
sum(PcsLatePer) as PcsLatePer, PlannedDlvTime, ActualLeadTime, ReversedQty, GrLotsRejected,
StockRejected, sum(StandardExtPrice) as StandardExtPrice, sum(CurrentExtPrice) as CurrentExtPrice,
sum(Variance) as Variance, PlannedPrice, undlvry_tlrnc_lmt, ovrdlvry_tlrnc_lmt
FROM POLines
Where POLines.VendorID = '1049086'
And POLines.PlantID = '2040'
And (POLines.PostingDate Between '1/1/2006' And '1/31/2006')
And (deliveryDays between 1 and 5)
group by PlantID, VendorName, PurchaseDoc, POrg, MaterialID, MatDesc, ConsignItms,
Item, ScheduleID, FirstFillRate, ScheduledQty, AppliedQty, OrderDate,
PostingDate, StatDlvDate, CurrentPrice, HistAvgPrice, PcsEarlyPer, PcsOnTimePer,
PcsLatePer, PlannedDlvTime, ActualLeadTime, ReversedQty, GrLotsRejected,
StockRejected, StandardExtPrice, CurrentExtPrice,
Variance, PlannedPrice, undlvry_tlrnc_lmt, ovrdlvry_tlrnc_lmt

If I run this query I get a summed AppliedQty field.

Select sum(AppliedQty) as AppliedQty
FROM POLines
Where POLines.VendorID = '1049086'
And POLines.PlantID = '2040'
And (POLines.PostingDate Between '1/1/2006' And '1/31/2006')
And (deliveryDays between 1 and 5)

Hdiva
Starting Member

10 Posts

Posted - 2006-06-27 : 15:42:54

Problem solved. Problem was in group by.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 16:24:38