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 transféré -----
--
Jacques Brignon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
