alternate between multiple auto-increment sequences using group in SQL

Question

Is it possible to run different auto-increment sequences based on another column? Like the left table instead of the right...

item_id  |  group_id           item_id  |  group_id
____________________           ____________________
    1    |     1                   1    |     1
    2    |     1                   2    |     1
    1    |     2                   3    |     2
    1    |     3                   4    |     3
    3    |     1                   5    |     1
    2    |     3                   6    |     3

I suspect that this is not possible but maybe there's a way to do this?

Thank you for your kind attention.

EDIT: for a 2012 SQL Server

If it's MySQL, see http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment/5416667#5416667 — Barmar, Feb 20 '15 at 22:55
With [a modern DBMS](http://use-the-index-luke.com/de/blog/2015-02/modern-sql) this can easily be achieved using window functions. — , Feb 20 '15 at 23:11
Its not a sequence number more, it is more of computed column — M.Ali, Feb 20 '15 at 23:40
Thanks, @M.Ali! That's what I need and I can do some digging into that - I wasn't making much progress finding an answer with this line of question — jlisham, Feb 21 '15 at 14:01

Pரதீப் · Answer 1 · 2015-02-21T02:39:22.423

2

Looks like Row_Number with partition by should work

select Row_number() over(partition by group_id order by (select null)) item_id,
       Group_id
From   Yourtable

SqlFiddle Demo

edited Feb 21 '15 at 02:39

answered Feb 21 '15 at 02:31

Pரதீப்

91,748
19
131
172

Thanks, @NoDisplayName: This does number correctly but as far as I can tell it doesn't create a static item_id? – jlisham Feb 21 '15 at 12:53

score 0 · Answer 2 · answered Feb 21 '15 at 06:31

0

not needed.

Just Use below quesry.

SELECT item_id, ROW_NUMBER() OVER(ORDER BY group_id)
FROM table_name

See: Row_Number(), Rank(),Dense_Rank()

Similar Example

answered Feb 21 '15 at 06:31

A_Sk

4,532
3
27
51

score 0 · Answer 3 · answered Feb 21 '15 at 17:20

Based on the direction proposed by M.Ali, I started looking at computed columns which didn't pan out either but it did get me thinking about another approach. I ended up with the following solution: far from elegant but it works...

INSERT INTO tst (group_id, item_id) VALUES (8,
CASE WHEN NOT EXISTS 
  (SELECT TOP(1) item_id FROM tst WHERE group_id=8 ORDER BY item_id DESC) 
   THEN 100 
  ELSE 
  (SELECT TOP(1) item_id+1 FROM tst WHERE group_id=8 ORDER BY item_id DESC) END)

If this gets you thinking about a better solution, please share :)

Thank you to all who took the time to respond.

alternate between multiple auto-increment sequences using group in SQL

3 Answers3