MySQL ODBC Connector - Show row number

Author

Show row number

Clyde Lewis

2006-01-26, 4:56 pm

-- ====================
=_20015546==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

Is there a command in mysql that will return the row number. I
tried rownum and rownum()

-- ====================
=_20015546==.ALT--

SGreen@unimin.com

2006-01-26, 4:56 pm

--=_alternative 006804DC85257101_=
Content-Type: text/plain; charset="US-ASCII"

Clyde Lewis <cllewis@gpi.com> wrote on 01/25/2006 01:41:30 PM:

> Is there a command in mysql that will return the row number. I
> tried rownum and rownum()
>

No, "rows" do not exist in the base data of a MySQL database. They are
called "records" and records may be stored in any order. Within some MySQL
storage engines (particularly InnoDB), more than one of any "record" may
exist in the database at the same time (depending on transaction activity)
or in more than one place (NDB).

Within the results of a SELECT query, the individual row number is only
important to the client. You need to use whatever number your client
library provides for you.( ex: in ADO you would use the
Recordset.AbsolutePosition property) There is no internal "record number"
that is exposed through any interface to any client.

Now, there is a pointer to each record but that is not useful information
to any system except the database server itself and cannot be used to
query for a particular record because it is not part of the data but it is
part of the metadata associated with each record. Records can move around
within a database file (changing their pointers) so long as the data
pointed to does not change.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 006804DC85257101_=--

Clyde Lewis

2006-01-26, 4:56 pm

-- ====================
=_25579906==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

Excellent explanation. Thanks again.

CL

At 02:00 PM 1/25/2006, SGreen@unimin.com wrote:

>Clyde Lewis <cllewis@gpi.com> wrote on 01/25/2006 01:41:30 PM:
>
>
>No, "rows" do not exist in the base data of a MySQL database. They
>are called "records" and records may be stored in any order. Within
>some MySQL storage engines (particularly InnoDB), more than one of
>any "record" may exist in the database at the same time (depending
>on transaction activity) or in more than one place (NDB).
>
>Within the results of a SELECT query, the individual row number is
>only important to the client. You need to use whatever number your
>client library provides for you.( ex: in ADO you would use the
>Recordset.AbsolutePosition property) There is no internal "record
>number" that is exposed through any interface to any client.
>
>Now, there is a pointer to each record but that is not useful
>information to any system except the database server itself and
>cannot be used to query for a particular record because it is not
>part of the data but it is part of the metadata associated with
>each record. Records can move around within a database file
>(changing their pointers) so long as the data pointed to does not change.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine

********************
***
Clyde Lewis
Database Administrator
General Parts, Inc.
919-227-5100

-- ====================
=_25579906==.ALT--

Little, Tim

2006-01-26, 4:56 pm

Alternatively, you could try something like this :

SET @rownum =3D 0;
SELECT @rownum :=3D @rownum + 1 AS rownumber_column,=20

some_real_columnname
s
FROM your_table=20
LIMIT 100

-----Original Message-----
From: Clyde Lewis & #91;mailto:cllewis@g
pi.com]
Sent: Wednesday, January 25, 2006 3:14 PM
To: SGreen@unimin.com
Cc: MySQL List
Subject: Re: Show row number

Excellent explanation. Thanks again.

CL

At 02:00 PM 1/25/2006, SGreen@unimin.com wrote:

>Clyde Lewis <cllewis@gpi.com> wrote on 01/25/2006 01:41:30 PM:
>
>
>No, "rows" do not exist in the base data of a MySQL database. They=20
>are called "records" and records may be stored in any order. Within=20
>some MySQL storage engines (particularly InnoDB), more than one of=20
>any "record" may exist in the database at the same time (depending=20
>on transaction activity) or in more than one place (NDB).
>
>Within the results of a SELECT query, the individual row number is=20
>only important to the client. You need to use whatever number your=20
>client library provides for you.( ex: in ADO you would use the=20
>Recordset.AbsolutePosition property) There is no internal "record=20
>number" that is exposed through any interface to any client.
>
>Now, there is a pointer to each record but that is not useful=20
>information to any system except the database server itself and=20
>cannot be used to query for a particular record because it is not=20
>part of the data but it is part of the metadata associated with=20
>each record. Records can move around within a database file=20
>(changing their pointers) so long as the data pointed to does not =
change.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine

********************
***
Clyde Lewis
Database Administrator
General Parts, Inc.
919-227-5100

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links