Finding 1st of a set in SQL

[MCubitt]

I have a long SQL query with a number of tables:

select
	substr(ipis.contract,1,3) Company,
	ipis.contract,
	ip.type_designation,
	ipis.part_no,
	ipis.lot_batch_no,
	ipis.serial_no,
	ipis.qty_onhand,
	ipc.inventory_value Std_Cost,
	ith.dated Receipt_Date,
	pol.currency_type,
	pol.currency_code,
	pol.buy_unit_price
from
	ifsapp.inventory_part_in_stock_tab ipis,
	ifsapp.inventory_part_tab ip,
	ifsapp.inventory_part_config_tab ipc,
	ifsapp.inventory_transaction_hist_tab ith,
	ifsapp.purchase_order_line_tab pol
where
	ipis.part_no = ip.part_no and
	ipis.contract = ip.contract and
	ipis.part_no = ipc.part_no and 
	ipis.contract = ipc.contract and
	ith.order_type (+) = 'PUR ORDER' and
	ith.part_no (+) = ipis.part_no and
	ith.contract (+) = ipis.contract and
	ith.lot_batch_no (+) = ipis.lot_batch_no and
	ith.serial_no (+) = ipis.serial_no and
 	pol.order_no (+) = ith.order_no and
 	pol.line_no (+) = ith.release_no and
	pol.release_no (+) = ith.sequence_no and
 	ipis.qty_onhand > 0  and 
	ipis.contract like 'OKM%'
	and ipis.part_no = '1111718'
order by
	ipis.contract, ipis.part_no, ipis.lot_batch_no, ipis.serial_no, receipt_date

The results look something like this:

Company	Contract	Type_Designation	Part_No	Lot_Batch_No	Serial_No	Qty_Onhand	Std_Cost	Receipt_Date	Currency_Type	Currency_Code	Buy_Unit_Price
OKM	OKMDM	5	1111718	*	W101262E	1	100				
OKM	OKMDM	5	1111718	*	W101401	1	100				
OKM	OKMDM	5	1111718	*	W201698	1	100				
OKM	OKMDM	5	1111718	*	W201878	1	100				
OKM	OKMDM	5	1111718	*	W402318	1	100	2004-05-27-13.14.53	1	EUR	120
OKM	OKMDM	5	1111718	*	W402318	1	100	2004-05-27-13.17.33	1	EUR	119
OKM	OKMDM	5	1111718	*	W402318	1	100	2004-05-27-13.17.33	1	EUR	120
OKM	OKMDM	5	1111718	*	W900144/R	1	100				
OKM	OKMMN	5	1111718	*	W502661	1	100	2005-03-16-13.37.56	1	EUR	124
OKM	OKMMN	5	1111718	*	W502661	1	100	2005-03-16-13.38.02	1	EUR	124
OKM	OKMMN	5	1111718	*	W502661	1	100	2005-03-16-13.38.02	1	EUR	144
OKM	OKMMN	5	1111718	*	W502662	1	100	2005-03-16-13.37.56	1	EUR	100
OKM	OKMMN	5	1111718	*	W502662	1	100	2005-03-16-13.38.11	1	EUR	100
OKM	OKMMN	5	1111718	*	W502662	1	100	2005-03-16-13.38.11	1	EUR	100
OKM	OKMMN	5	1111718	*	W502672	1	100	2005-03-16-13.49.35	1	EUR	101
OKM	OKMMN	5	1111718	*	W502672	1	100	2005-03-16-13.50.07	1	EUR	101
OKM	OKMMN	5	1111718	*	W502672	1	100	2005-03-16-13.50.07	1	EUR	101

What I want to see is all the ones with blank receipt date BUT only the 1st occurrence per company/contract/destype/part_no/lot/serial/qty/stdcost to get the original purchase price (of 1st Purchase Order).. giving me:

Company	Contract	Type_Designation	Part_No	Lot_Batch_No	Serial_No	Qty_Onhand	Std_Cost	Receipt_Date	Currency_Type	Currency_Code	Buy_Unit_Price
OKM	OKMDM	5	1111718	*	W101262E	1	100				
OKM	OKMDM	5	1111718	*	W101401	1	100				
OKM	OKMDM	5	1111718	*	W201698	1	100				
OKM	OKMDM	5	1111718	*	W201878	1	100				
OKM	OKMDM	5	1111718	*	W402318	1	100	2004-05-27-13.14.53	1	EUR	120
OKM	OKMDM	5	1111718	*	W900144/R	1	100				
OKM	OKMMN	5	1111718	*	W502661	1	100	2005-03-16-13.37.56	1	EUR	124
OKM	OKMMN	5	1111718	*	W502662	1	100	2005-03-16-13.37.56	1	EUR	100
OKM	OKMMN	5	1111718	*	W502672	1	100	2005-03-16-13.49.35	1	EUR	101

Any ideas please?





Applications Support
UK

 

[PruSQLer]

Can you select all the rows with blanks in them, then do a UNION to a query where you do a max on the receipt date?

 

[MCubitt]

Its ok, distinct did the trick, thx


Applications Support
UK