I keep forgeting to reply all. Sorry
- Message transféré de Jacques Brignon [EMAIL PROTECTED] -
Date : Tue, 31 Jan 2006 13:33:22 +0100
De : Jacques Brignon [EMAIL PROTECTED]
Adresse de retour :Jacques Brignon [EMAIL PROTECTED]
Sujet : RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon
in a result set - Bayesian Filter detected spam
À : Gordon Bruce [EMAIL PROTECTED]
Thanks. That looks OK from the point of view of generating the row number. But
when this is done if my result set is tens of thousands row long, how do I find
the row number of the record satisfying a conditon (like name = something)
without storing the result of the query in a temp table and query that table to
find the row and its number?
--
Jacques Brignon
Selon Gordon Bruce [EMAIL PROTECTED]:
You can use a user variable [EMAIL PROTECTED] in the sample below} to number
the rows in
the result set.
mysql set @row:=0;
Query OK, 0 rows affected (0.00 sec)
mysql select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10;
+--+-+
| @row:[EMAIL PROTECTED] | city_Name |
+--+-+
|1 | !fajji !fasan |
|2 | 'aadeissa |
|3 | 'abas |
|4 | 'abas |
|5 | 'abasabad |
|6 | 'abd al qader |
|7 | 'abdullah kalay |
|8 | 'abdullah kalay |
|9 | 'abruyeh|
| 10 | 'adel bagrou|
+--+-+
10 rows in set (0.00 sec)
-Original Message-
From: Jacques Brignon [mailto:[EMAIL PROTECTED]
Sent: Monday, January 30, 2006 9:19 AM
To: mysql@lists.mysql.com
Subject: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in
a result set - Bayesian Filter detected spam
Oops! forgoten to include the list in the relply
--
Jacques Brignon
- Message transféré de Jacques Brignon [EMAIL PROTECTED] -
Date : Mon, 30 Jan 2006 16:16:53 +0100
De : Jacques Brignon [EMAIL PROTECTED]
Adresse de retour :Jacques Brignon [EMAIL PROTECTED]
Sujet : Re: Finding the row number satisfying a conditon in a result set
À : Jake Peavy [EMAIL PROTECTED]
Selon Jake Peavy [EMAIL PROTECTED]:
On 1/30/06, Jacques Brignon [EMAIL PROTECTED] 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
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.
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
Thanks for the tip, I am going to think to it as I do not see right away how
this solves the problem.
I agree with your comment, This is precisely because the result row number is
not in the database that I need to find it.
The problem I am trying to solve is the following:
A query returns a result set with a number of rows, lets say 15000 as an
example.
I have an application wich displays those 10 by 10 with arrows based
navigation
capabilities (first page, previous page, next page, last page).
I also have a search capability and I need to find in which set of 10 results
the row I search for will be diplayed in order to show directly the
appropriate
page and to know what is the rank of this row in the result set or in the
page
to show the searched result row selected.
As an example the row having a customer id of 125, would have the row # 563
in
the result set (not orderd by customer id but by some other criterion like
name) and would therefore be displayed in the page showing result rows 561 to
570
When I say row I do not mean a row in any table but a row in the result set
produced by the query which can touch several tables.
None of the fields of the result set contains the row number, it is just the
number of time I have to loop through the result set to get the row in the
set
which matches my criterion.
I hope this makes my question clearer.
I am sure this is a pretty common problem, but I have not yet figured out the
clever way to tackle it!
--
Jacques Brignon
- Fin du message transféré -
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
- Fin du message
