| Gordon Bruce 2006-01-30, 11:24 am |
| You can use a user variable {@row in the sample below} to number the =
rows in the result set.
mysql> set @row:=3D0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @row:=3D@row+1, city_Name from citiesw limit 10;
+--------------+-----------------+
| @row:=3D@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]=20
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=E9r=E9 de Jacques Brignon <jacbrignon@online.fr> =
-----
Date=A0: Mon, 30 Jan 2006 16:16:53 +0100
De=A0: Jacques Brignon <jacbrignon@online.fr>
Adresse de retour=A0:Jacques Brignon <jacbrignon@online.fr>
Sujet=A0: Re: Finding the row number satisfying a conditon in a result =
set
=C0=A0: Jake Peavy <djstunks@gmail.com>
Selon Jake Peavy <djstunks@gmail.com>:
> On 1/30/06, Jacques Brignon <jacbrignon@online.fr> wrote:
rank[color=darkred]
identifier[color=dar
kred]
same[color=darkred]
the[color=darkred]
sorted in[color=darkred]
set,[color=darkred]
very[color=darkred]
>
>
>
> 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=E9r=E9 -----
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql? unsub...
rint.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|