Auto incrementing a column based on another
romeo, before I craft a reply, I want to ask: Is there any chance you have the opportunity to edit the schema in this case?

If you can, you definitely should, because the problems you're running into are because of denormalization.

Normalization of the schema

From your example table above, it appears this should be split into at least two separate tables. From the information you've given you should have an invoice table and a client table, with (pseudocode) definitions as

CREATE TABLE invoice (invoice_id, client_id, invoice_date);


and

CREATE TABLE customer (client_id,service_id, phone_number);


Using these tables, you can establish a foreign key relationship from invoice (client_id) to customer (client_id), allowing a many-to-one relationship between invoice and client.

Subsequently, you can obtain all invoices pertaining to a particular client via

SELECT * FROM invoice JOIN client ON invoice.client_id = clinet.client_id
WHERE client = ...


Hope this helps!