Re: How to get the last record from the slected record set
--On 22 August 2004 20:31 +0800 Manisha Sathe [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
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 ? Cycle them all until you hit the last one? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? regards Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
* Manisha Sathe I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? Try this: order by score desc LIMIT 9,1 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
Manisha Sathe wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? regards Manisha order by score desc limit 9,1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the last record from the slected record set
--On 22 August 2004 21:05 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? I'm not quite sure I follow what you're aiming for, but have you tried... limit 10,1 i.e. return 1 record, starting at row 10 in the result set. Beyond that you'd have to explain what 'score' is, as it's getting a little confusing... :) -Kp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get the last record from the slected record set
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:[EMAIL PROTECTED] Sent: 22 August 2004 14:08 To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: How to get the last record from the slected record set - Thanks
Thanks to all of, I could get it Thanks Manisha - Original Message - From: Karl Pielorz [EMAIL PROTECTED] To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 23, 2004 9:52 PM Subject: Re: How to get the last record from the slected record set --On 22 August 2004 21:05 +0800 Manisha Sathe [EMAIL PROTECTED] wrote: I want to select first 10 records out of 100. And then get the 10th position. If i make use of order by score asc limit 1 then I will get 100th record, How shall i pick up 10th position? I'm not quite sure I follow what you're aiming for, but have you tried... limit 10,1 i.e. return 1 record, starting at row 10 in the result set. Beyond that you'd have to explain what 'score' is, as it's getting a little confusing... :) -Kp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
