List:General Discussion« Previous MessageNext Message »
From:Kerry Frater Date:August 23 2004 2:36pm
Subject:RE: How to get the last record from the slected record set
View as plain text  
Hi,

I don'e know if this will help as I am probably only one step ahead of you
here, but if it does great. If not you can just delete it.

How are you going to access the data? Via a program, PHP, using queries?

I am a newbie to this area myself, and use Delphi.

I know that using Delphi with the DB components I can simply tell the
Navigator to get the last record. Fairly straight forward. I have recently
found that using the DB componensts (so I am told) creates a local dataset
which means that ALL rows selected are transferred to a local dataset for
you to have a "simple" goto last record. i.e. If we have 1 million rows,
each row contains 10 integers then opening a table to the data and telling
it to "go to last record" will cause 10 million integers on the server to
transfer to a local dataset on your PC/workstation for you to process. This
is not really an issue if the datasets are created on the same computer as
the Server.

(I am learning that) if data transfer is an issue and could cause problems
then a little bit of SQL is more than useful. I continue to learn about the
SQL formats. With the help of another newish MySQL writer (he is one step
ahead of me) Tom gave me some help that I have turned into this.

For workstations needing to gain access to 1 row at a time and NOT wanting
to create a large dataset on my workstation, which may have a slow
connection I do the following (in pseudo code):

Get First Record
      SQL.Text = SELECT * FROM MyTable Where MyField > "" ORDER BY MyIndex
LIMIT 1;
      ExecuteSQL.Text;

Get Last Record
      SQL.Text = SELECT * FROM MyTable ORDER BY MyIndex DESC LIMIT 1;
      ExecuteSQL.Text;

When I get a record I remember the unique value of the index that I am
scrolling through. Let us say I store the value of "MyField" of the current
row in a variable called "MyKey" then the next record is where MyField >
MyKey and you limit the number of rows to return by 1.

Get Next Record
      SQL.Text = SELECT * FROM MyTable Where MyField > MyKey ORDER BY
MyIndex LIMIT 1;
      ExecuteSQL.Text;
      if Dataset.IsEmpty then GetLastRecord(MyMatter,MyIndex);

Get Previous Record
      SQL.Text = SELECT * FROM MyTable Where MyField < MyKey ORDER BY
MyIndex DESC LIMIT 1;
      ExecuteSQL.Text;
      if Dataset.IsEmpty then GetFirstRecord(MyMatter,MyIndex);

The above constructs work if the column you are scrolling through is unique.
The issue is when you have a non unique order e.g. Surname. This is where
Tom's more advanced knowledge of SQL helped be get over the problem.

SELECT * FROM table WHERE MyField >= '' AND UniqueId <> -1 ORDER BY
MyField,UniqueId LIMIT 0,1

You will need to have a column containing a Unigue ID to do this (indexing
this will also give you extra performance)
The clever bit is that we are creating a temporary sort order which is a
combination of the required field sequence and the unique ID sequence which
will, by definition, give us a order with a "Unique" sort sequence.

Now you will need to know the values from the current Row for the columns
MyField and UnigueID, let us say OldMyField and OldUniqueID. We will then
get

Get the next record:
SELECT * FROM table WHERE MyField >= OldMyField AND UniqueId > OldUniqueID
ORDER BY MyField,UniqueId LIMIT 1

The other Get Record types are then derivations, but you should be able to
write generic function/subtroutines based upon what you want. This is what I
am doing at the moment and the performance over PC's using slow connection
links to not so fast servers is proving to be quite successful.

For my not very big tables I will probably not bother to implement the calls
as I can use the generic components to write quick interfaces as the local
dataset issue won't be a problem.

I hope this makes sense!


Kerry


-----Original Message-----
From: Manisha Sathe [mailto:manisha@stripped]
Sent: 22 August 2004 14:08
To: mysql@stripped
Subject: Re: How to get the last record from the slected record set


yes, but is there any better way of doing it ?

regards
Manisha


----- Original Message -----
From: "Karl Pielorz" <kpielorz@stripped>
To: "Manisha Sathe" <manisha@stripped>; <mysql@stripped>
Sent: Monday, August 23, 2004 8:34 PM
Subject: Re: How to get the last record from the slected record set


>
>
> --On 22 August 2004 20:31 +0800 Manisha Sathe <manisha@stripped>
> wrote:
>
> > I am having more than 10 records in a table. I want to select only first
> > top 10 records (depending on one field score) and then want to select
> > 10th position record.
> >
> > select * from table1 order by score desc LIMIT 10
> >
> > This will give me 10 records but then how to get the last record ?
>
> order by score asc limit 1
>
> [i.e. turn it around and pick the 1st (which will be the last because it's
> ordered the other way)]
>
> :-)
>
> -Kp
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
How to get the last record from the slected record setManisha Sathe23 Aug
  • Re: How to get the last record from the slected record setKarl Pielorz23 Aug
  • Re: How to get the last record from the slected record setMartijn Tonies23 Aug
  • Re: How to get the last record from the slected record setManisha Sathe23 Aug
    • Re: How to get the last record from the slected record setRoger Baklund23 Aug
    • Re: How to get the last record from the slected record setKarl Pielorz23 Aug
  • Re: How to get the last record from the slected record setManisha Sathe23 Aug
    • RE: How to get the last record from the slected record setKerry Frater23 Aug
  • Re: How to get the last record from the slected record setgerald_clark23 Aug
  • Re: How to get the last record from the slected record set - ThanksManisha Sathe23 Aug