MySQL Forums
Forum List  »  General

Join to return the first instance - cannot use group by
Posted by: m m
Date: September 09, 2014 03:35PM

Hello
I have the following subquery where i need to use the first instance of each invoice per job. for example
each consignment can have multiple invoices;

consignment Invoice
1 111
1 112
2 113
3 114
3 115

I'm only interested in the first instance of each invoice per consignment.e.g.

1 111
2 113
3 114

I have tried adding group by CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$ to the end of this query, however it is causing the query to run for a very long time and doesn't complete


I believe I need to do something with the JOIN , however I have tried the various join types , right / left / inner / outter none of which change the results.

I'm not very experienced with complicated queries and I cannot figure this out.

Any ideas?


SET @START_DATE := "2014-08-27";
SET @END_DATE:= "2014-09-02";

SELECT distinct
CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$

FROM
CONSIGNMENT_ALL_HEADER_UNS
JOIN INVOICE_HEADER_UNS ON CONSIGNMENT_ALL_HEADER_UNS.OPSREF$$ = INVOICE_HEADER_UNS.OUR_REF

WHERE
CONSIGNMENT_ALL_HEADER_UNS.TMODE$$ = '6'
AND TRANSACTION_STATUS = 9
#AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ not in ('1' , '4', '8', '7')

AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ <>'7'

AND CONSIGNMENT_ALL_HEADER_UNS.CONS_TYPE$$ in ('3' , '5', '9')

AND (@START_DATE IS NULL OR INVOICE_HEADER_UNS.INVOICE_DATE_D8>=@START_DATE) AND
(@END_DATE IS NULL OR INVOICE_HEADER_UNS.INVOICE_DATE_D8<=@END_DATE OR INVOICE_HEADER_UNS.INVOICE_DATE_D8 IS NULL)

#AND (@START_DATE IS NULL OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE >=(@START_DATE)) AND
#(@END_DATE IS NULL OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE <=@END_DATE OR SUPPLIER_COSTS_UNS.ESTIMATE_DATE IS NULL))

Options: ReplyQuote


Subject
Written By
Posted
Join to return the first instance - cannot use group by
m m
September 09, 2014 03:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.