Hello,

I am trying to figure out how to assign a value of 1 to a record and it will increment by one while the customer number is still the same. Once the customer number changes it will assign that new record for that customer a one and increment by one and so on.

Below is a table that shows the data structure. The filed Count Inv by Cust is the output i am trying to get.

customer invoice amount Count_Inv_By_Cust
1 111 10.00 1
1 112 150.00 2
1 113 145.00 3
2 114 110.00 1
2 115 75.00 2



All help is appreciated.

what database are you using? this isn't an ANSI SQL question

if it's msaccess, see this thread

if it's mysql, that feature is built in to the auto_increment facility

if it's some other database, you may want to investigate doing it in your application logic, e.g. with a transaction lock on retrieving the last number used

but the real question here is: why do your numbers have to have that format?

what's wrong with invoice numbers that are not magically tied to the customer?

Well the reason why i am trying to do this is because i am trying to come up with a method where i can compare every customers first invoice to their second invoice.

If there is an easier wahy to do this the idea is more than welcome. This was just one of the first solutions i thought of.

Thanks Again.

every customer's first invoice is the invoice for that customer with the lowest date

every customer's second invoice is the invoice for that customer with the lowest date that isn't the lowest date

of course, this means you need to add a date to your invoice table

Okay. The table does have a datetime field for the invoice date.

So i guess i would select the min(invoice_date) and min(invoice_numb) for the customers first invoice.

So to get the second invoice I would do the same but i can't be the same invoice as the one selected first. Let me see what i can come up with to get the second part.

Thanks Again.

here's the customer's first invoice --
the second invoice involves another nesting

I ran into a similar problem... in general, given a table Invoices like yours:

SELECT P.*, (SELECT COUNT(*) FROM Invoices Q WHERE P.customer = Q.customer AND P.invoice_date> Q.invoice_date) AS InvoiceOrder FROM Invoices P

Err... maybe that should be a less than sign. Anyway, save it as a view and you can find the Nth invoice per customer quite easily.

Hello,

I am trying to figure out how to assign a value of 1 to a record and it will increment by one while the customer id is still the same. Once the customer id changes it will assign that new record for that customer a 1 and increment by one and so on.

Below is a table that shows the data structure. The filed Count Inv by Cust is the output i am trying to get.

customer invoice amount Count_Inv_By_Cust
1 111 10.00 1
1 112 150.00 2
1 113 145.00 3
2 114 110.00 1
2 115 75.00 2



All help is appreciated.

You can use ROW_NUMBER() to do this.

SELECT Customer,
Invoice,
Amount,
ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Invoice) AS RecID
FROM TableName

Perfect! That worked exactly the way i needed it to.

Thank you very much!