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]