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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
