MySQL get next unique value without auto increment
What is interesting about this situation can be solved using the MyISAM storage.

I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site

CREATE TABLE site_workorder_seq
(
    SiteID int not null,
    SiteWorkorderNum int not null auto_increment,
    PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;


Here is a sample loading into this table:

mysql&gt;     DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)

mysql&gt;     CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)

mysql&gt;     USE david
Database changed
mysql&gt;     CREATE TABLE site_workorder_seq
    -&gt;     (
    -&gt;         SiteID int not null,
    -&gt;         SiteWorkorderNum int not null auto_increment,
    -&gt;         PRIMARY KEY (SiteID,SiteWorkorderNum)
    -&gt;     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql&gt;     INSERT INTO site_workorder_seq (SiteID) VALUES
    -&gt;     (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
    -&gt;     (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql&gt;     SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
16 rows in set (0.00 sec)

mysql&gt;


Let's look at the the last WorkorderNum from each site

mysql&gt; SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -&gt; FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                4 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.05 sec)

mysql&gt;


Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:

INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;


Let's run this and see what happens

mysql&gt; INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
    -&gt; FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.03 sec)

mysql&gt; SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                1 |
|      1 |                2 |
|      2 |                1 |
|      2 |                2 |
|      2 |                3 |
|      2 |                4 |
|      3 |                1 |
|      3 |                2 |
|      3 |                3 |
|      3 |                4 |
|      3 |                5 |
|      4 |                1 |
|      4 |                2 |
|      4 |                3 |
|      4 |                4 |
|      5 |                1 |
|      5 |                2 |
+--------+------------------+
17 rows in set (0.00 sec)

mysql&gt; SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
    -&gt; FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
|      1 |                2 |
|      2 |                4 |
|      3 |                5 |
|      4 |                4 |
|      5 |                2 |
+--------+------------------+
5 rows in set (0.00 sec)

mysql&gt;


As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.