| Jacques Brignon 2006-01-31, 7:23 am |
| I keep forgeting to "reply all". Sorry
----- Message transféré de Jacques Brignon <jacbrignon@online.fr> -----
Date_: Tue, 31 Jan 2006 13:33:22 +0100
De_: Jacques Brignon <jacbrignon@online.fr>
Adresse de retour_:Jacques Brignon <jacbrignon@online.fr>
Sujet_: RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon
in a result set - Bayesian Filter detected spam
À_: Gordon Bruce <gordon@mailprint.com>
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 <gordon@mailprint.com>:
> You can use a user variable {@row 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:=@row+1, city_Name from citiesw limit 10;
> +--------------+-----------------+
> | @row:=@row+1 | 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 & #91;mailto:jacbrigno
n@online.fr]
> 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 <jacbrignon@online.fr> -----
> Date_: Mon, 30 Jan 2006 16:16:53 +0100
> De_: Jacques Brignon <jacbrignon@online.fr>
> Adresse de retour_:Jacques Brignon <jacbrignon@online.fr>
> Sujet_: Re: Finding the row number satisfying a conditon in a result set
> À_: Jake Peavy <djstunks@gmail.com>
>
> Selon Jake Peavy <djstunks@gmail.com>:
>
> identifier
>
> 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/mysql? unsub...ri
nt.com
>
>
>
----- 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/mysql? unsub...sie.nctu.edu.tw
|