Need a quick row number on your query output?

    Recently on Reddit someone asked how to automatically get a row number generated in front of the output from a query.  There is a ROW_NUMBER() function and it very simple to use with Windowing Functions.

    The sample data for this example is very simple.

> SELECT name, qty FROM sample;
+------+-----+
| name | qty |
+------+-----+
| a    |   5 |
| b    |   2 |
| c    |   4 |
+------+-----+
3 rows in set (0.0019 sec)

    There are two way to write Windowing Functions.  The simplest is to add the ROW_NUMBER() function and  OVER() keyword to the query in the middle of the query. 

> select row_number() over () as 'no', 
         name, 

         qty 

  from sample;
+----+------+-----+
| no | name | qty |
+----+------+-----+
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
+----+------+-----+
3 rows in set (0.0011 sec)

    Or define the window at the end of the query. I prefer this version for readability reasons.

> select row_number() over w as 'no', 

         name, 

         qty 

from sample 

window w as();
+----+------+-----+
| no | name | qty |
+----+------+-----+
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
+----+------+-----+
3 rows in set (0.0009 sec)

    So now you know how to add a simple row number to your query output and two ways to format that request.