MySQL Custom Variable Incrementation

Question

I have an issue similar to Select incremented integer but I'm trying to take it a step further.

I have a table (a history table that tracks changes to some code) that has, among other things, an ID (id), a modified timestamp (modified), and a foreign key from another table (company_id). If I specify a single company_id, I can get my query to increment a value numbering from 1 to X which order the items happened, but it's only for the specific company_id.

So this...

    SELECT id, (@rev := @rev+1) AS revision, company_id
    FROM code_history AS ch
    INNER JOIN (SELECT @rev := 0) AS r
    WHERE company_id = '1'

Returns...

    ID | revision | company_id
     1 |    1     |     1
     3 |    2     |     1
     4 |    3     |     1
     7 |    4     |     1
     9 |    5     |     1
    12 |    6     |     1

What I don't know is how to make it so that each unique company_id has it's own incremented list of ID's and revision counts without making a query to the database for each company_id, or even if it's possible.

Data I'd Like...

    ID | revision | company_id
     1 |    1     |     1
     3 |    2     |     1
     4 |    3     |     1
     7 |    4     |     1
     9 |    5     |     1
    12 |    6     |     1
     2 |    1     |     2
     8 |    2     |     2
    13 |    3     |     2
    18 |    4     |     2
    19 |    5     |     2
    21 |    6     |     2
     5 |    1     |     3
     6 |    2     |     3
    10 |    3     |     3
    11 |    4     |     3
    14 |    5     |     3

I appreciate any and all help, thank you!

It seems like you should be ordering by ch.ID, for consistency. — Marcus Adams
– Marcus Adams, Commented Feb 1, 2012 at 21:22
That's something I didn't include in here, and I apologize. In my actual I have it ordering by the modified date to ensure that they're numbered in the order in which they happened. — Lisa
– Lisa, Commented Feb 1, 2012 at 21:25

DRapp · Accepted Answer · 2012-02-01 21:29:39Z

3

You were on the right track, you would just need a second variable to preserve what the LAST company ID was, then reset the counter back to 1

SELECT 
      PreSortedRevisions.id, 
      @rev := if( @lastCompany = PreSortedRevisions.company_id, @rev+1, 1 ) AS revision, 
      @lastCompany := company_id as Company_ID
   FROM 
      ( select ch.company_id,
               ch.id 
            from code_history AS ch
            order by ch.company_id,
                     ch.id ) PreSortedRevisions,
      (SELECT @rev := 0, @lastCompany := 0) AS SqlVars
   order by
      Company_id,
      Revision

First, the inner query pre-gathers the records and sorts them by company and ID so they are returned in proper sequence per company.

Then, by testing the IF() before the @LastCompany := company_id assignment, the @LastCompany starts with 0, and will be different the first time through starting the @rev = 1. After the first record, the @LastCompany will now be whatever the last one was, be the same on record 2 and increase the counter. When the company actually changes, it resets the counter back to 1.

answered Feb 1, 2012 at 21:29

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lisa Over a year ago

Sir, you might be the coolest person ever. This does exactly what I want it to do. Thank you!!

DRapp Over a year ago

@Lisa, you like that... take a look at a REAL stretch I did for another post here... stackoverflow.com/questions/9057820/…

Lisa Over a year ago

I've been sitting here trying to think of a response to that other post, but all I can come up with is "Holy crap!".

DRapp Over a year ago

@Lisa, yup... makes your mind bend a bit huh... on what you CAN do... That one was a totally new approach for me, but logistically works...

Collectives™ on Stack Overflow

MySQL Custom Variable Incrementation

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related