Mysql – How to generate auto increment values for the duplicate column in the sql

MySQLmysql-5.5

How to display when i was run to find duplicate values from my table at this time i want to add new column in select statement with auto increment values

SELECT * FROM tutorials_tbl
WHERE Lscno IN (
SELECT Lscno
FROM tutorials_tbl
GROUP BY Lscno
HAVING COUNT(Lscno) > 1
)ORDER BY Lscno

ID       Title          LSCNo
10       titl_14    000000/06/3/1944
19       titl_13    000000/06/3/19
561     titl_1      000000/10/125/1955
671     titl_1      000000/10/125/1955
1765    titl_1      000000/06/65/1976
1834    titl_1      000000/06/65/1976
1909    titl_1      000000/06/3/1971
1910    titl_1      000000/06/3/1971

 **Excepted output like** in MY SQL ?




 Add
NewCol    ID    Title   LSCNo
  1       10    titl_14 000000/06/3/1944
  2       19    titl_13 000000/06/3/19
  3       561   titl_1  000000/10/125/1955
  4       671   titl_1  000000/10/125/1955
  5       1765  titl_1  000000/06/65/1976
  6       1834  titl_1  000000/06/65/1976
  7       1909  titl_1  000000/06/3/1971
  8       1910  titl_1  000000/06/3/1971

Best Answer

Try this one, execute one at a time set variable and select statement.

SET @Variable:=0;
SELECT @Variable:=@Variable+1 AS AddNewColumn,ID,Title,Lscno 
FROM tutorials_tbl
WHERE Lscno IN (
    SELECT Lscno
    FROM tutorials_tbl
    GROUP BY Lscno
    HAVING COUNT(Lscno) > 1
)ORDER BY Lscno

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – Getting duplicate primary key for auto increment column

Related Question