SQl Query for S.no

Question

How to use Sql query for generating S.no from 1. I can't get the answer correctly

SELECT (@cnt := @cnt + 1) AS 'S.No',`barcode`,
(SELECT @cnt := 0) AS dummy
FROM  wp_weblib_outitems

Answer 1

Try this:

SELECT  @s:=@s+1 AS 'S.No',`barcode`
FROM    wp_weblib_outitems,
        (SELECT @s:= 0) AS s;

Answer 2

This is my query. i just want S.No as asc . How can i write this query as ascending order

SELECT DISTINCT (subject),a.title, a.callnumber, 
    a.author, a.recv_date,a.pubdate ,b.book_access_number,(@cnt := @cnt + 1) AS 'S.No'
    from wp_weblib_collection a
    inner join wp_weblib_book_log_reports b on a.barcode = b.book_access_number
    $student
    CROSS JOIN (SELECT @cnt := 0) AS dummy
    GROUP BY subject asc 

Answer 3

You can try with ROW_NUMBER() as given below.......

SELECT ROW_NUMBER() OVER (ORDER BY [Column1]) AS 'NO',[Column1],[column2],.... FROM tblUser;