Professional Web Applications Themes

Query result very long, how do I enumerate displayed results? - MySQL

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....

  1. #1

    Default Query result very long, how do I enumerate displayed results?

    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 Guest

  2. #2

    Default Re: Query result very long, how do I enumerate displayed results?

    Matchy wrote: 

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


    Paul Guest

  3. #3

    Default Re: Query result very long, how do I enumerate displayed results?

    On Sun, 11 Feb 2007 21:03:11 -0000, "Paul Lautman"
    <com> wrote:
     
    >
    >SET RNUM = 0;
    >SELECT RNUM:=RNUM+1 row_number, * FROM ...
    >[/ref]

    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 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
    $
    Matchy Guest

  4. #4

    Default Re: Query result very long, how do I enumerate displayed results?

    On 12 Feb, 16:48, Matchy <com> wrote: [/ref]

    >
    > 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 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
    > $[/ref]

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


    Captain Guest

  5. #5

    Default Re: Query result very long, how do I enumerate displayed results?



    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)



    John Guest

  6. #6

    Default Re: Query result very long, how do I enumerate displayed results?

    On 14 Feb, 06:20, John C. <com> wrote: 
    >
    > 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)[/ref]

    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.

    Captain Guest

Similar Threads

  1. Replies: 4
    Last Post: February 12th, 04:23 PM
  2. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  3. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  4. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  5. How to strip of characters when long text is displayed?
    By WestSide in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 11th, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139