MySQL ODBC Connector - Finding the row number satisfying a conditon in a result set

Author

Finding the row number satisfying a conditon in a result set

Jacques Brignon

2006-01-30, 9:25 am

I would like some advice on the various and best ways of finding the rank of the
row which satisfies a given condition in a rsult set.

Let's assume that the result set includes a field containing an identifier from
one of the table used in the query and that not two rows have the same value
for this identifier but that the result set does not contains all the
sequential values for this identifier and/or the values are not sorted in any
predictable order.

The brute force method is to loop through all the rows of the result set, until
the number is found to get the rank of the row. That does not seem very clever
and it can be very time consuming if the set has a lot of rows.

--
Jacques Brignon

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

Jake Peavy

2006-01-30, 9:25 am

------=_Part_7496_8552750.1138633013891
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 1/30/06, Jacques Brignon <jacbrignon@online.fr> wrote:
>
> I would like some advice on the various and best ways of finding the rank
> of the
> row which satisfies a given condition in a rsult set.
>
> Let's assume that the result set includes a field containing an identifie=
r
> from
> one of the table used in the query and that not two rows have the same
> value
> for this identifier but that the result set does not contains all the
> sequential values for this identifier and/or the values are not sorted in
> any
> predictable order.
>
> The brute force method is to loop through all the rows of the result set,
> until
> the number is found to get the rank of the row. That does not seem very
> clever
> and it can be very time consuming if the set has a lot of rows.

use ORDER BY with a LIMIT of 1

your subject line needs work though - a "row number" has no meaning in a
relational database.

-jp

------=_Part_7496_8552750.1138633013891--

Sponsored Links