Hello group,

How would I go about numbering rows from an SQL query?

For example, if I use the query:

SELECT myCol FROM myTable ORDER BY myOtherCol;

it returns:

+-------+
| row x |
| row y |
| row z |
+-------+

Now I would like to get:

SELECT [something], myCol FROM myTable ORDER BY myOtherCol;
+-----------+
| 1 | row x |
| 2 | row y |
| 3 | row z |
+-----------+

....or...

SELECT [something], myCol FROM myTable ORDER BY myOtherCol DESC;
+-----------+
| 1 | row z |
| 2 | row y |
| 3 | row x |
+-----------+



Thanks,
Max

On Oct 26, 9:15 am, Max <Maxime.Pla...@gmail.com> wrote:
> Hello group,
>
> How would I go about numbering rows from an SQL query?
>
> For example, if I use the query:
>
> SELECT myCol FROM myTable ORDER BY myOtherCol;
>
> it returns:
>
> +-------+
> | row x |
> | row y |
> | row z |
> +-------+
>
> Now I would like to get:
>
> SELECT [something], myCol FROM myTable ORDER BY myOtherCol;
> +-----------+
> | 1 | row x |
> | 2 | row y |
> | 3 | row z |
> +-----------+
>
> ...or...
>
> SELECT [something], myCol FROM myTable ORDER BY myOtherCol DESC;
> +-----------+
> | 1 | row z |
> | 2 | row y |
> | 3 | row x |
> +-----------+
>
> Thanks,
> Max

Answer to myself (I hope it can help others)
taken from: http://forums.mysql.com/read.php?10,36490,36511

SET @row = 0;
SELECT @row := @row + 1 AS rowNumber, myCol FROM myTable ORDER BY
myOtherCol DESC;