What's the shortest method to generate a column of numbers for queries instead of having to count each rows in SQL?

Question

I'm going through some practice questions and had a question asking for number of rows shown as the result of my query and found myself counting each rows for it and thought it was inefficient.

How do I create a new column that numbers the rows from 1 to number of rows?

If my query is as follows,

SELECT *
FROM invoices
WHERE BillingCountry = 'Germany' AND Total > 5

then the result is:

+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+
| InvoiceId | CustomerId | InvoiceDate         | BillingAddress          | BillingCity | BillingState | BillingCountry | BillingPostalCode | Total |
+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+
|        12 |          2 | 2009-02-11 00:00:00 | Theodor-Heuss-Straße 34 | Stuttgart   |         None | Germany        | 70174             | 13.86 |
|        40 |         36 | 2009-06-15 00:00:00 | Tauentzienstraße 8      | Berlin      |         None | Germany        | 10789             | 13.86 |
|        52 |         38 | 2009-08-08 00:00:00 | Barbarossastraße 19     | Berlin      |         None | Germany        | 10779             |  5.94 |
|        67 |          2 | 2009-10-12 00:00:00 | Theodor-Heuss-Straße 34 | Stuttgart   |         None | Germany        | 70174             |  8.91 |
|        95 |         36 | 2010-02-13 00:00:00 | Tauentzienstraße 8      | Berlin      |         None | Germany        | 10789             |  8.91 |
|       138 |         37 | 2010-08-23 00:00:00 | Berger Straße 10        | Frankfurt   |         None | Germany        | 60316             | 13.86 |
|       193 |         37 | 2011-04-23 00:00:00 | Berger Straße 10        | Frankfurt   |         None | Germany        | 60316             | 14.91 |
|       236 |         38 | 2011-10-31 00:00:00 | Barbarossastraße 19     | Berlin      |         None | Germany        | 10779             | 13.86 |
|       241 |          2 | 2011-11-23 00:00:00 | Theodor-Heuss-Straße 34 | Stuttgart   |         None | Germany        | 70174             |  5.94 |
|       269 |         36 | 2012-03-26 00:00:00 | Tauentzienstraße 8      | Berlin      |         None | Germany        | 10789             |  5.94 |
|       291 |         38 | 2012-06-30 00:00:00 | Barbarossastraße 19     | Berlin      |         None | Germany        | 10779             |  8.91 |
|       367 |         37 | 2013-06-03 00:00:00 | Berger Straße 10        | Frankfurt   |         None | Germany        | 60316             |  5.94 |
+-----------+------------+---------------------+-------------------------+-------------+--------------+----------------+-------------------+-------+

There are 12 rows of information pulled from a dataset, but I only realized it after manually counting the rows.

What can I add in my query that can add a column in the left-most side of the result that shows numbers 1 through 12 for each rows like how Excel would show it as and is there a way to do the same for the columns but in an alphabetical order?

Answer 1

I would use ROW_NUMBER function:

SELECT ROW_NUMBER() OVER (ORDER BY BillingAddress, BillingCity) AS RN, *
FROM invoices
WHERE BillingCountry = 'Germany' AND Total > 5