Distinct Question

[txwylde]

I am trying to build a table that lists products by their material number and their desecription. I am pulling this information from another table. My problem is I am getting mulitple Material Numbers with the same description.


Here is my query:
create table pricing
select distinct material_num, product_group, amt_charged, sales_org from daily_order_summary
where quantity = 1
and material_num is not null;

Is there a way to get just 1 material number? I thought by using the Distinct function this would allow me to do this.

Thanks!
Bill

 

[azzazzello]

DISTINCT gets unique entries. If you want to limit your output to just 1 row (I think that's what you want?), use LIMIT.

So,

select material_num, product_group, 
amt_charged, sales_org 
from daily_order_summary
where quantity = 1
and material_num is not null
LIMIT 1;

 

[txwylde]

I do not want to limit to just 1 row. If I runt he query as is I get about 13000 records. I then pull a Material Number up and have 7 records when I should just have one.
take care
Bill

 

[Kozusnik]

Could you give an example of the data?

It sounds to me like the data may stop it from being unique.

Have you tried
<code>
SELECT DISTINCT material_num FROM daily_order_summary
WHERE quantity = 1
AND material_num IS NOT NULL;
</code>

You should get one row per material_num.

Mark