List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 30 2006 3:47pm
Subject:Re: Trans.: Re: Finding the row number satisfying a conditon in a result
View as plain text  
Jacques Brignon <jacbrignon@stripped> wrote on 01/30/2006 10:18:59 AM:

> Oops! forgoten to include the list in the relply
> --
> Jacques Brignon
> ----- Message transféré de Jacques Brignon <jacbrignon@stripped>
> -----
>    Date : Mon, 30 Jan 2006 16:16:53 +0100
>      De : Jacques Brignon <jacbrignon@stripped>
> Adresse de retour :Jacques Brignon <jacbrignon@stripped>
>   Sujet : Re: Finding the row number satisfying a conditon in a result 
>       À : Jake Peavy <djstunks@stripped>
> Selon Jake Peavy <djstunks@stripped>:
> > On 1/30/06, Jacques Brignon <jacbrignon@stripped> wrote:
> > >
> > > I would like some advice on the various and best ways of finding the 
> > > of the
> > > row  which satisfies a given condition in a rsult set.
> > >
> > > Let's assume that the result set includes a field containing an 
> > > from
> > > one of the table used in the query and that not two rows have the 
> > > value
> > > for this identifier but that the result set does not contains all 
> > > 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 
> > > until
> > > the number is found to get the rank of the row. That does not seem 
> > > 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 
> > relational database.
> >
> > -jp
> >
> Thanks for the tip, I am going to think to it as I do not see right away 
> 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 
> 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 
> 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 
> produced by the query which can touch several tables.
> None of the fields of the result set contains the row number, it is just 
> 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é -----

Yes, that is much clearer. Assuming that your results ARE ordered by some 
criteria (such as by name) so that the sequence of one query execution 
closely resembles that of another then you can artificially create a 
sequence number by saving those results into a temporary table with an 
auto_increment column.

  rownum int unsigned auto_increment
  , name varchar(50) not null,
  , ... other columns in your results ...
  , primary_key (rownum)
  , key(name)

INSERT tmpResults (name,... other columns ...)
SELECT name, ... other columns ...
... (rest of query) ...;

Now you have somewhere that has a row number on each row of your query. In 
most applications, it is more efficient to either send the whole recordset 
to the client and display the results in pages based on the cached results 
or to run a smaller query of just those fields you want to search by and 
send them to the client as a form of index. Then the client can ask the 
server for the FULL query and use the LIMIT offsets you had from the 
"index" query. 

What it boils down to is this:

a) What you have described may look user friendly but it is database 
intensive. Your application performance will probably suffer.
b) Most queries only ask for what they actually need. If you only wanted 
results that match a certain name or other condition, only ask for those 
rows. That may mean modifying your client so that it only asks for the 
rows the user wants to see. 
c) If you want your user to "scroll through" a set of results you have 
three simple options:
  1) pull them all on the first query and navigate the results client-side 
(very fast, quite scalable, most flexible)
  2) re-execute the query multiple times on the server and work with each 
separate result set. This can become highly intensive if what you actually 
wanted to show is in the 100th iteration of the query. 
  3) use a temporary (or static) table to cache and serialize your 
results. Use it to navigate to the subset of records you seek.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Trans.: Re: Finding the row number satisfying a conditon in a result setJacques Brignon30 Jan
  • Re: Trans.: Re: Finding the row number satisfying a conditon in a resultsetSGreen30 Jan