SQL Server
To return a row number with record we have ROW_NUMBER() in SQL which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax:
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , … [ n ] ] order_by_clause )
MY SQL
Here I am explaining how to return row number in my sql
For Example we have following table
CREATE TABLE tblSales( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, fruit VARCHAR(32), amount DECIMAL ); INSERT INTO tblSales VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23), (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15);
SELECT fruit, amount FROM tblSales ORDER BY amount DESC; If we run this query it will result like +--------+--------+ | fruit | amount | +--------+--------+ | cherry | 124 | | plum | 23 | | pear | 19 | | apple | 13 | | banana | 4 | | orange | 2 | +--------+--------+
Now here is the query with ranking SET @rank=0; SELECT @rank:=@rank+1 AS rank, fruit, amount FROM tblSales ORDER BY amount DESC; +------+--------+--------+ | rank | fruit | amount | +------+--------+--------+ | 1 | cherry | 124 | | 2 | plum | 23 | | 3 | pear | 19 | | 4 | apple | 13 | | 5 | banana | 4 | | 6 | orange | 2 | +------+--------+--------+
Hope it helps!