2

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
1

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
  • if answer is right ,please tick answer is correct – Pydi Raju Sep 1 '14 at 8:29
0

try this

SELECT @i:=@i+1 AS iterator, t.ID, t.Title ,t.LSCNo FROM (SELECT ID, Title, LSCNo FROM tutorials_tbl WHERE Lscno IN ( SELECT Lscno FROM tutorials_tbl GROUP BY Lscno HAVING COUNT(Lscno) > 1 )ORDER BY Lscno) AS t, (SELECT @i:=0) AS foo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.