Fw: Display information
- Original Message - From: "Ron Beck" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 16, 2001 7:12 PM Subject: Re: Display information > SELECT id,lot_id,lot_type FROM scrap_table > ORDER BY id limit 1932,10; > > seemed promising but it put them in oldest-to-newest order (ascending), > not newest-to-oldest order (desc). When the "desc" qualifier was added, > I got records 122 thru 113. Again, not exactly what I was looking for. You only have to know the total number of record ,which I think you can easily get SELECT id,lot_id,lot_type FROM scrap_table ORDER BY id DESC limit TOT - 1932,10 at the present time I don't remember if ther is a way to do it directly from SQL, but I don't think it's such a great effort ^^ > What I was actually hoping for was something I could add to my original > SELECT statement to perform the task if someone entered the ID but would > show all (limit 10) if there was no entry. And, I wanted to do it > without rewriting my code. Unfortunately, it looks like I may have to > do a code rewrite for that section, which means documenting, testing, > acceptance... > > Anyway, thanks for all the suggestions > Ron > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Display information
Hello all again. I think I need to be a little more specific in describing my problem. So, let me try again. I have a database of scrap records. There are currently over 2000 records in this database. I have a web page that queries this database and displays the records in reverse order, newest to oldest. I use this query to do that... SELECT id,lot_id,lot_type FROM scrap_table ORDER BY id DESC LIMIT 0,10 which displays the last 10 records. If I want the next 10 records, I change the limit statement to look like this... LIMIT 10,10 which starts at the 10th record recovered and displays another 10, again from newest to oldest. I have a button on my web page that increments the number of records (LIMIT <##>,10) with each click on the "Pg Up" or "Pg Dn" button. Now, I don't want to page through eight or nine pages to get down to the record I'm looking for. I want to generate a query to display a range of 10 records starting with my entry. Now, the following query will work... SELECT id,lot FROM scrap_table WHERE id >=1880 and id <= 1890 ORDER BY id desc; but I don't always have the starting record number to work with. Now, the following suggestion seemed promising... SELECT id,lot_id,lot_type FROM scrap_table ORDER BY id limit 1932,10; seemed promising but it put them in oldest-to-newest order (ascending), not newest-to-oldest order (desc). When the "desc" qualifier was added, I got records 122 thru 113. Again, not exactly what I was looking for. What I was actually hoping for was something I could add to my original SELECT statement to perform the task if someone entered the ID but would show all (limit 10) if there was no entry. And, I wanted to do it without rewriting my code. Unfortunately, it looks like I may have to do a code rewrite for that section, which means documenting, testing, acceptance... Anyway, thanks for all the suggestions Ron suggestions vie email * > select id,lot_id,lot_type from scrap_table where id >= 1932 order by id > limit 10; > select id,lot_id,lot_type from scrap_table order by id limit 1932,10; > > either should work, i think the first would be faster but I'm not positive > how MySQL would handle the second in terms of optimization. > > -jm ** > If you simply needed the exact result here's the query > > SELECT id,lot_id,lot_type FROM scrap_table WHERE > id BETWEEN 1932 AND 1942 > ORDER BY id DESC ** > isn't it as simple as... > > select id,lot_id,lot_type from scrap_table > where id >= 1932 and id <= 1942 > order by id limit 10; > > ? > > cheers, > > P *** > Have a look in the manual, it says LIMIT [offset], rows, so you should say: > > order by id limit 1932, 10; > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Display information
Have a look in the manual, it says LIMIT [offset], rows, so you should say: order by id limit 1932, 10; SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]select_expression,... [INTO OUTFILE 'file_name' export_options][FROM table_references [WHERE where_definition][GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] Tobias. -Ursprüngliche Nachricht- Von: Ron Beck [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 16. Februar 2001 17:23 An: [EMAIL PROTECTED] Betreff: Display information Hello all, I have a database with a little over 2000 records. I want to be able to construct a query that says something like this... select id,lot_id,lot_type from scrap_table (part I need help with) order by id limit 10; which should give me records 1932 - 1942 like this... +--+--+--+ | id | lot_id | lot_type | +--+--+--+ | 1942 | 0034906 | F| | 1941 | 0100308 | F| | 1940 | 0036309 | S| | 1939 | 0033503 | F| | 1938 | 0034108E | D| | 1937 | 0034712 | F| | 1936 | 0029404 | F| | 1935 | 0032706 | F| | 1934 | 0029404 | F| | 1933 | 0100307 | F| | 1932 | 0100404 | F| +--+--+--+ any suggestions? Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Display information
isn't it as simple as... select id,lot_id,lot_type from scrap_table where id >= 1932 and id <= 1942 order by id limit 10; ? cheers, P > > which should give me records 1932 - 1942 like this... > > +--+--+--+ > | id | lot_id | lot_type | > +--+--+--+ > | 1942 | 0034906 | F| > | 1941 | 0100308 | F| > | 1940 | 0036309 | S| > | 1939 | 0033503 | F| > | 1938 | 0034108E | D| > | 1937 | 0034712 | F| > | 1936 | 0029404 | F| > | 1935 | 0032706 | F| > | 1934 | 0029404 | F| > | 1933 | 0100307 | F| > | 1932 | 0100404 | F| > +--+--+--+ > > > any suggestions? > > Ron > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Display information
> select id,lot_id,lot_type from scrap_table > (part I need help with) >order by id limit 10; > > which should give me records 1932 - 1942 like this... > > +--+--+--+ > | id | lot_id | lot_type | > +--+--+--+ > | 1942 | 0034906 | F| > | 1941 | 0100308 | F| > | 1940 | 0036309 | S| > | 1939 | 0033503 | F| > | 1938 | 0034108E | D| > | 1937 | 0034712 | F| > | 1936 | 0029404 | F| > | 1935 | 0032706 | F| > | 1934 | 0029404 | F| > | 1933 | 0100307 | F| > | 1932 | 0100404 | F| > +--+--+--+ If you simply needed the exact result here's the query SELECT id,lot_id,lot_type FROM scrap_table WHERE id BETWEEN 1932 AND 1942 ORDER BY id DESC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Display information
select id,lot_id,lot_type from scrap_table where id >= 1932 order by id limit 10; select id,lot_id,lot_type from scrap_table order by id limit 1932,10; either should work, i think the first would be faster but I'm not positive how MySQL would handle the second in terms of optimization. -jm -Original Message- From: Ron Beck [mailto:[EMAIL PROTECTED]] Sent: Friday, February 16, 2001 8:23 AM To: [EMAIL PROTECTED] Subject: Display information Hello all, I have a database with a little over 2000 records. I want to be able to construct a query that says something like this... select id,lot_id,lot_type from scrap_table (part I need help with) order by id limit 10; which should give me records 1932 - 1942 like this... +--+--+--+ | id | lot_id | lot_type | +--+--+--+ | 1942 | 0034906 | F| | 1941 | 0100308 | F| | 1940 | 0036309 | S| | 1939 | 0033503 | F| | 1938 | 0034108E | D| | 1937 | 0034712 | F| | 1936 | 0029404 | F| | 1935 | 0032706 | F| | 1934 | 0029404 | F| | 1933 | 0100307 | F| | 1932 | 0100404 | F| +--+--+--+ any suggestions? Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Display information
Hello all, I have a database with a little over 2000 records. I want to be able to construct a query that says something like this... select id,lot_id,lot_type from scrap_table (part I need help with) order by id limit 10; which should give me records 1932 - 1942 like this... +--+--+--+ | id | lot_id | lot_type | +--+--+--+ | 1942 | 0034906 | F| | 1941 | 0100308 | F| | 1940 | 0036309 | S| | 1939 | 0033503 | F| | 1938 | 0034108E | D| | 1937 | 0034712 | F| | 1936 | 0029404 | F| | 1935 | 0032706 | F| | 1934 | 0029404 | F| | 1933 | 0100307 | F| | 1932 | 0100404 | F| +--+--+--+ any suggestions? Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php