0

I have an SQL table with two fields as primary key (ID & RX), RX has AUTO_INCREMENT. This results in the following SQL table:

 ID    |  RX
------------------
 1     |     1
 1     |     2
 2     |     3
 2     |     4

However, I expected the following result :

 ID    |  RX
------------------
 1     |     1
 1     |     2
 2     |     1
 2     |     2

What is wrong in my configuration? How can I fix this? Thanks!

Dispersia
  • 1,436
  • 9
  • 23
John McKenna
  • 9
  • 1
  • 1
    Why would you expect auto_increment to go 1, 2, 1, 2 – Dispersia Nov 03 '16 at 19:01
  • AUTO_INCREMENT is not valid sql server syntax. In sql server we have identity. Regardless, it does NOT reset for a new value in another column. – Sean Lange Nov 03 '16 at 19:01
  • 1
    auto increment isn't going to handle this for you. You'll have to query for the max RX for a given ID and increment it yourself. – juharr Nov 03 '16 at 19:02

1 Answers1

0

You cant do that with autoincrement.

But using your current table you can calculate the second id, and use a VIEW to show the second ID

 SELECT ID, RX, 
        ROW_NUMBER() OVER (PARTITION BY ID
                           ORDER BY RX) as RX_2
 FROM yourTable
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118