RE: LIMIT Question
Have your where clause select the customer, sort result by autoincrement column DESC limit 50 Only problem is the order would be the inverse (i.e., last in first displayed) from your goal. Only way I can thing to go around this would be to select into a temp table and then sort that into the desired sequence. Just a thougth. Dirk Bremer said: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. The goal of the query, which currently returns all of the results, is to find all entries for a given customer regardless of when they occurred. I would like no more than the last 50 rows inserted for this customer (this could be based upon the auto-increment value) and would prefer to have them ordered within the 50-possible results in the order they were inserted, from lowest-ID to the highest-ID. This will prevent the query from showing possible hundreds of results. There are multiple customers in the table. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:28 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT Question
Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- 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: LIMIT Question
Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. The goal of the query, which currently returns all of the results, is to find all entries for a given customer regardless of when they occurred. I would like no more than the last 50 rows inserted for this customer (this could be based upon the auto-increment value) and would prefer to have them ordered within the 50-possible results in the order they were inserted, from lowest-ID to the highest-ID. This will prevent the query from showing possible hundreds of results. There are multiple customers in the table. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:28 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- 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: LIMIT Question
On Wednesday 28 June 2006 01:39 pm, Dirk Bremer wrote: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. That's why ORDER BY id DESC is used, it basically flips your table reverse, so that last inserted (in essence the highest ID) is first, all the way down to the first inserted (the lowest ID). Then LIMIT 50 will give you 50 from highest id to lowest ID, or the last 50. -- Chris White PHP Programmer/DBlonde Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT Question
Dirk, you could try this: (SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50) ORDER BY customertableid ASC; Like one sometimes does with UNIONs, but without any UNIONs. Didn't know whether it would work, but it does (on 5.0.21 anyway). That will give you the 50 entries with the highest ID numbers, sorted lowest to highest. Dan On 6/28/06, Chris White [EMAIL PROTECTED] wrote: On Wednesday 28 June 2006 01:39 pm, Dirk Bremer wrote: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. That's why ORDER BY id DESC is used, it basically flips your table reverse, so that last inserted (in essence the highest ID) is first, all the way down to the first inserted (the lowest ID). Then LIMIT 50 will give you 50 from highest id to lowest ID, or the last 50. -- Chris White PHP Programmer/DBlonde Interfuel -- 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: LIMIT Question
Dan, Close, but there appears to be some differences under 4.1 which are interesting to say the least. Using: SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50 I get the expected result, i.e. 50 ordered in reverse. Using: (SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50) ORDER BY customertableid ASC; I get the full set (272 results) ordered in reverse. Here is the query: set @job_coop = 'B28013'; set @cycle = 1; set @type = 2; set @test = 1; set @cur_date=date_sub(curdate(),interval 7 day); (select ftp_transfers.queue.ident as 'ID', ftp_transfers.queue.job_coop as 'JCoop', ftp_transfers.queue.cycle as 'Cyc', lpad(ftp_transfers.queue.status,10,' ') as 'Status', case when (ftp_transfers.queue.type = 1) then 'Internal' when (ftp_transfers.queue.type = 2) then 'PDF To Coop' when (ftp_transfers.queue.type = 3) then 'iVUE Zip To Coop' when (ftp_transfers.queue.type = 4) then 'iVUE Zip To Mandan' endas 'Type', if(ftp_transfers.queue.test = 0,'No','Yes') as 'Test', lpad(format(ftp_transfers.queue.file_size,0),11,' ') as 'Size', substring(date_format(ftp_transfers.queue.queue_time,'%Y-%m-%d %T'),12,8) as 'q_time', ftp_transfers.queue.file_time as 'f_time', substring(ftp_transfers.queue.transfer_start,12,8) as 't_start', substring(ftp_transfers.queue.transfer_end,12,8) as 't_end' from ftp_transfers.queue where ftp_transfers.queue.job_coop = @job_coop and ftp_transfers.queue.type = @type # and ftp_transfers.queue.test = @test # and ftp_transfers.queue.cycle = @cycle # and (@cur_date = ftp_transfers.queue.queue_time) # and param5 not like '%ebi%' order by ftp_transfers.queue.ident desc limit 50) order by ftp_transfers.queue.ident asc I'm not in an immediate position to upgrade to 5.x at this point in time and this is a non-critical issue for me. Thanks for you help and advice. If someone has another solution, please chime in. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:54 To: Chris White Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Dirk, you could try this: (SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50) ORDER BY customertableid ASC; Like one sometimes does with UNIONs, but without any UNIONs. Didn't know whether it would work, but it does (on 5.0.21 anyway). That will give you the 50 entries with the highest ID numbers, sorted lowest to highest. Dan On 6/28/06, Chris White [EMAIL PROTECTED] wrote: On Wednesday 28 June 2006 01:39 pm, Dirk Bremer wrote: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. That's why ORDER BY id DESC is used, it basically flips your table reverse, so that last inserted (in essence the highest ID) is first, all the way down to the first inserted (the lowest ID). Then LIMIT 50 will give you 50 from highest id to lowest ID, or the last 50. -- Chris White PHP Programmer/DBlonde Interfuel -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT question
Andrey [EMAIL PROTECTED] wrote: I have a following question: I have a table with thousands of rows which i need to show in a String Grid ( I use Borland Delphi with MyDac component which implements MySQL interface). If i make a query SELECT * FROM tbl WHERE col1=something, it can cause retreival of 10,000 records and my program will just eat all the Windows resources and die :) So i need to retreive first 1000 of rows, then on user's request, next 1000, etc. If i use SELECT ... LIMIT 1000, it just retreives first 1000 of records, which doesn't resolve my problem. Use SELECT .. LIMIT 1000, 1000 to retrieve the next 1000 rows: http://www.mysql.com/doc/en/SELECT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
