Consecutive Ids

Description


You work for an online store in which each item put up for sale gets a unique randomly generated id.

These item ids are stored in the itemIds table with only one column:

  • id: unique id of an item.

However, this system proved to be not very convenient to use in a number of queries that required consecutive ids. To solve this problem, you decided to generate new ids for the items using the following algorithm: the item with the smallest id would get 1 as a new id, the second smallest would get 2, and so on.

Given the itemIds table, compose the resulting table with two columns: oldId and newId. The first column should contain the old item id, and the second one should contain the new id generated as described above. The table should be sorted by the newId in ascending order.

Example

For the following tables itemIds

id
1
12
23
42
49
678
3242
9320
67867
84523

the output should be

oldId newId
1 1
12 2
23 3
42 4
49 5
678 6
3242 7
9320 8
67867 9
84523 10
  • [execution time limit] 10 seconds (mysql)

Solution


1
2
3
4
5
6
7
8
/*Please add ; after each select statement*/
CREATE PROCEDURE consecutiveIds()
BEGIN
    SET @id = 0;
	SELECT  id AS oldId, @id:=@id + 1 AS newId
    FROM itemIds
    ORDER BY id;
END