Need help on how to remove duplicate records from showing up in a view.

[jtm311]

Is there a simple way to remove duplicates from showing up in my table?
You see below in my SQL statement I have 1 main table and 3 joins my goal is to only show (link) the first record of the "Oder_entry_lines table".

The "Group By" would be great however I've learned that you need to show all the fields or it will not work.

Thanks

SELECT DISTINCT order_entry.po_date, order_entry.sample AS sample2, shipping_details_b.READYTOSHIP AS Readytoship, order_entry.ship, order_entry.requested_delivery_date, order_entry.customer_name, order_entry.customer_main, order_entry.status, order_entry.customer_pono, order_entry.prod1, order_entry.sales_rep, salesorder_b.PONUMBER AS Ponumber, order_entry.rush,  if( order_entry.sample = .T. , 'Sample', '')  AS Sample, order_entry.Join1, order_entry_lines.DESCRIPTION AS Description 
FROM (order_entry order_entry
	 LEFT OUTER JOIN order_entry_lines order_entry_lines
		 ON  order_entry.customer_pono = order_entry_lines.CUSTOMER_PO 
	 LEFT OUTER JOIN salesorder_b salesorder_b
		 ON  order_entry.customer_pono = salesorder_b.PONUMBER 
	 LEFT OUTER JOIN shipping_details_b shipping_details_b
		 ON  order_entry.customer_pono = shipping_details_b.PONUMBER ) 
WHERE order_entry.status <> 'Processed'  OR  order_entry.customer_pono <> salesorder_b.PONUMBER 
GROUP BY order_entry.po_date, order_entry.sample, shipping_details_b.READYTOSHIP, order_entry.ship, order_entry.requested_delivery_date, order_entry.customer_name, order_entry.customer_main, order_entry.status, order_entry.customer_pono, order_entry.prod1, order_entry.sales_rep, salesorder_b.PONUMBER, order_entry.rush,  if( order_entry.sample = .T. , 'Sample', '') , order_entry.Join1, order_entry_lines.DESCRIPTION 
ORDER BY order_entry.requested_delivery_date

[lvasic]

Re: Need help on how to remove duplicate records from showing up in a view.

You could try this type of code:
Select e.po_date,l.Customer_PO
from order-entry e
Left Join order_entry_Lines l on l.customer_po = e.customerpono and l.customer_po = (Select top customer_po from order_entry_Lines where customer_po = e.customerpono order by customer_po DESC)

[jtm311]

Re: Need help on how to remove duplicate records from showing up in a view.

You could try this type of code:
Select e.po_date,l.Customer_PO
from order-entry e
Left Join order_entry_Lines l on l.customer_po = e.customerpono and l.customer_po = (Select top customer_po from order_entry_Lines where customer_po = e.customerpono order by customer_po DESC)

Thanks your reply helped me find the problem! It turns out when using the SQL Genie and using the grouped by you must have the Primary key fields listed first. So when I read the above I seen the date first :)
Once I correct it the table now only shows the one record.

Thanks again for the response.