2

Given this table

id      secId
 1          2
 2          2
 3          2
 4          1
 5          3
 6          3

i would like to add a new column "sortIndex" and init it's values to an ascending int value for each set of equal secId's. So the resulting table would be

id      secId       sortIndex
 1          2               1
 2          2               2
 3          2               3
 4          1               1
 5          3               1
 6          3               2

So for every set of equal secId's I have a new sequence "1,2,3,4,..." If there is any chance a single query would be just awesome.

user3440145
  • 793
  • 10
  • 34
  • 1
    You want to emulate the analytic function `ROW_NUMBER() OVER()` which MySQL is lacking. See here: http://stackoverflow.com/questions/17939198/row-number-per-group-in-mysql – Thorsten Kettner May 02 '16 at 06:38
  • @ThorstenKettner Then I would have to first create a new column for sortIndex and then use the info in your pasted link to update the sortIndex-entries? – user3440145 May 02 '16 at 06:54
  • You shouldn't add a column for data which is implicitely already there. Create row numbers on-the-fly when you need them (that's only for display; you don't need them for mere sorting, as you can just as well order by id). – Thorsten Kettner May 02 '16 at 07:07
  • @ThorstenKettner yeah - no :-) Not in my case. I need the sort_index column to provide a manual sorting possibility for data in a frontend. This is a feature just implemented and therefore already existing db's have to be adapted and need to provide some initial ordering by the column "sort_index". So - I really do need this column to be inserted in an existing table and be auto-filled with sequences of ascending numbers. – user3440145 May 02 '16 at 07:15
  • Ah, okay, it's an initial sort oeder that can be changed later. Okay. If I remember correctly, MySQL has problems with accessing the same table in an update statement, so you may want to create a table temporarily (`create temp_table (id_secid, sortindex) select id, secid, @rownum ...`) and use this then to update your existing table (where you add the new column first of course). – Thorsten Kettner May 02 '16 at 08:07
  • @ThorstenKettner OK, that sounds reasonable. However - being not that deep in MySql I struggle applying your suggestion. Could you refine / make it more specific what I have to do and how the syntax could be? – user3440145 May 02 '16 at 09:26

2 Answers2

0

You are trying to simulate row_number in MySQL. You need to use variable.

SET @row_number:=0;
SET @sec_id:='';
SELECT id, @row_number:=CASE WHEN @secid=secid THEN @row_number+1 ELSE 1 END AS row_number, 
@secid:=secid AS secid
FROM your_table
ORDER BY secid;

You can find more details at

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

Give a row number.

Check for secId, if its same increment the @curRow by 1.

Query

select Id,secId,( 
  case secId 
  when @curA
  then @curRow := @curRow + 1 
  else @curRow := 1 and @curA := secId end
) as sort_index
from your_table_name t,
(select @curRow := 0, @curA := '') r
order by Id,secId;

SQL Fiddle Demo

Ullas
  • 11,450
  • 4
  • 33
  • 50
  • Thx, but this throws a couple of errors - what are the brackets around the second select for and what means the "r" in this same row? Also why is there a mapping for the table "your_table" to "t" if t is never used? And - what is the comma doing after "t"? Then - is this query meant to be used after a new column "sort_index" was created already? – user3440145 May 02 '16 at 07:11
  • @user3440145 : `t` and `r` are just aliases. – Ullas May 02 '16 at 07:16
  • @user3440145: The comma between tables (the comma after t) is an antiquated join syntax. You can replace it with `CROSS JOIN` to make the join explicit. – Thorsten Kettner May 02 '16 at 08:11