Reset Row Number For Each Group - SQL Server Vs MySQL

Continuing on my SQL Server vs MySQL series, we will see how same things can be done differently in SQL Server and MySQL

Generating a row number  or a serial number and resetting it on each group is a common requirement when showing result sets. In SQL Server, starting from version 2005, we can make use of the row_number() function with the partition clause

Consider the following set of data
sql-data

SQL Server


Using the row_number() function, we can generate the serial number and reset for each names

select row_number() over (partition by names order by names) as sno,names from test


MySQL


Using a variable, we can generate the serial number, and use another variable that resets first variable for each group

set @sno:=0;
set @names:='';


OUTPUT


result
at

3 comments:

  1. CottoraSeptember 29, 2012 at 7:20 AM

    Madhivanan - this is a very interesting way to reset the counter via MySql. However, I am having trouble getting the trigger to be "triggered".

    It seems that sno should be reset when the name is null. However when is the name ever null? Is rollup or some other break causing statement to be used so that name is caused to be null?

    Thanks you, Cottora

    ReplyDelete
  2. UnknownJanuary 24, 2014 at 1:15 PM

    http://stackoverflow.com/questions/2026956/want-row-number-on-group-of-column-in-my-sql

    Here's a way using ddl..

    ReplyDelete
  3. Nandakumar ASNovember 6, 2015 at 5:12 PM

    Madhivanan - the query helped me to resolve the issue to create the sequence number of my requirement. However, in the example displayed in your blog, how to avoid displaying sno for the rows where there is one record and display sno where there are more than 1 records with same name. Any suggestion would be great help.

    ReplyDelete