Let's say I have a list of 466 rows resulting from a query. How would
I number the results so that I know exactly which item is # 220?
Thanks.

Matchy wrote:
> Let's say I have a list of 466 rows resulting from a query. How would
> I number the results so that I know exactly which item is # 220?
> Thanks.

SET @RNUM = 0;
SELECT @RNUM:=@RNUM+1 row_number, * FROM ...

On Sun, 11 Feb 2007 21:03:11 -0000, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>Matchy wrote:
>> Let's say I have a list of 466 rows resulting from a query. How would
>> I number the results so that I know exactly which item is # 220?
>> Thanks.
>
>SET @RNUM = 0;
>SELECT @RNUM:=@RNUM+1 row_number, * FROM ...
>

I'm getting an error below using the test database 'world' available
from mysql.com? I'm using a mysql version for WinXP. What am I doing
wrong? Thanks!

--cut, cut--

mysql> use world;
Database changed
mysql> set @RNUM = 0;
Query OK, 0 rows affected (0.02 sec)

mysql> select @RNUM := @RNUM+1 row_number, * from city;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that
corresponds to your MySQL server version for the right syntax to use
near '* from city' at line 1
mysql> select * from city limit 5;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.19 sec)

mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.27-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
$ pwd
/C/wamp/mysql/bin
$ uname -a
UWIN-XP XXXXXX 4.1.0/5.1 2600 i686
$ cmd
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\wamp\mysql\bin>exit
$

On 12 Feb, 16:48, Matchy <noaddr...@incaseitaintobvious.com> wrote:
> On Sun, 11 Feb 2007 21:03:11 -0000, "Paul Lautman"
>
> <paul.laut...@btinternet.com> wrote:
> >Matchy wrote:
> >> Let's say I have a list of 466 rows resulting from a query. How would
> >> I number the results so that I know exactly which item is # 220?
> >> Thanks.
>
> >SET @RNUM = 0;
> >SELECT @RNUM:=@RNUM+1 row_number, * FROM ...
>
> I'm getting an error below using the test database 'world' available
> from mysql.com? I'm using a mysql version for WinXP. What am I doing
> wrong? Thanks!
>
> --cut, cut--
>
> mysql> use world;
> Database changed
> mysql> set @RNUM = 0;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> select @RNUM := @RNUM+1 row_number, * from city;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that
> corresponds to your MySQL server version for the right syntax to use
> near '* from city' at line 1
> mysql> select * from city limit 5;
> +----+----------------+-------------+---------------+------------+
> | ID | Name | CountryCode | District | Population |
> +----+----------------+-------------+---------------+------------+
> | 1 | Kabul | AFG | Kabol | 1780000 |
> | 2 | Qandahar | AFG | Qandahar | 237500 |
> | 3 | Herat | AFG | Herat | 186800 |
> | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
> | 5 | Amsterdam | NLD | Noord-Holland | 731200 |
> +----+----------------+-------------+---------------+------------+
> 5 rows in set (0.19 sec)
>
> mysql> select version();
> +---------------------+
> | version() |
> +---------------------+
> | 5.0.27-community-nt |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> exit
> Bye
> $ pwd
> /C/wamp/mysql/bin
> $ uname -a
> UWIN-XP XXXXXX 4.1.0/5.1 2600 i686
> $ cmd
> Microsoft Windows XP [Version 5.1.2600]
> (C) Copyright 1985-2001 Microsoft Corp.
>
> C:\wamp\mysql\bin>exit
> $

Try changing it to:
set @RNUM = 0;
select @RNUM := @RNUM+1 row_number, city.* from city;

>
>Try changing it to:
>set @RNUM = 0;
>select @RNUM := @RNUM+1 row_number, city.* from city;

I tried this using the world database and I also unsuccessfully
checked mysql's ab faq, googled and gave up and still don't
understand why the row number starts at 4080 (note: I think there
there are a total of 4079 items). How does one make it (row_number)
start counting at 1? Thank you.

mysql> select @rnum:=@rnum+1 row_number, city.* from city limit 4;
+------------+----+----------------+-------------+----------+------------+
| row_number | ID | Name | CountryCode | District | Population |
+------------+----+----------------+-------------+----------+------------+
| 4080 | 1 | Kabul | AFG | Kabol | 1780000 |
| 4081 | 2 | Qandahar | AFG | Qandahar | 237500 |
| 4082 | 3 | Herat | AFG | Herat | 186800 |
| 4083 | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+------------+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select city.* from city limit 4;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

On 14 Feb, 06:20, John C. <fakel...@fake.com> wrote:
> >Try changing it to:
> >set @RNUM = 0;
> >select @RNUM := @RNUM+1 row_number, city.* from city;
>
> I tried this using the world database and I also unsuccessfully
> checked mysql's ab faq, googled and gave up and still don't
> understand why the row number starts at 4080 (note: I think there
> there are a total of 4079 items). How does one make it (row_number)
> start counting at 1? Thank you.
>
> mysql> select @rnum:=@rnum+1 row_number, city.* from city limit 4;
> +------------+----+----------------+-------------+----------+------------+
> | row_number | ID | Name | CountryCode | District | Population |
> +------------+----+----------------+-------------+----------+------------+
> | 4080 | 1 | Kabul | AFG | Kabol | 1780000 |
> | 4081 | 2 | Qandahar | AFG | Qandahar | 237500 |
> | 4082 | 3 | Herat | AFG | Herat | 186800 |
> | 4083 | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
> +------------+----+----------------+-------------+----------+------------+
> 4 rows in set (0.00 sec)
>
> mysql> select city.* from city limit 4;
> +----+----------------+-------------+----------+------------+
> | ID | Name | CountryCode | District | Population |
> +----+----------------+-------------+----------+------------+
> | 1 | Kabul | AFG | Kabol | 1780000 |
> | 2 | Qandahar | AFG | Qandahar | 237500 |
> | 3 | Herat | AFG | Herat | 186800 |
> | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
> +----+----------------+-------------+----------+------------+
> 4 rows in set (0.00 sec)

Wierd,
I just tried
SET @RNUM =0;
SELECT @RNUM := @RNUM +1row_number, `pages` . *
FROM `pages` LIMIT 4
On a table with 18 records and the output was correctly numbered.