I have a SQL statement (then engine I'm using is MySQL) that looks like this:

select r.*, u.name as clinic from requests r, user u where u.user_id=r.clinic order by date desc limit 15,15

This the default query for the page, and it works fine. It represents page two of a bunch of medical billing data, ordered by date desc. The problem is, the individual results pages, which display some data in a table, are supposed to be sortable, by clicking an up or down arrow at the top of each column. I do this by making an AJAX call to another page, which calls the same function which generated the results the first time, but with a different sort order. So, for example, when I click the "patient name" column, it is supposed to sort by patient last name, and the SQL statement becomes this:

select r.*, u.name as clinic from requests r, user u where u.user_id=r.clinic order by last_name asc limit 15,15

The problem is, it now sorts ALL of the results by last_name before applying the 15,15 limit, which changes my second page results completely. Obviously this is logically what should happen with the above query, but I cannot seem to sort just the second page by last_name, or whatever, after the 15,15 limit has been applied to some other sort order.

I tried making the above SQL statements into this:

select r.*, u.name as clinic from requests r, user u where u.user_id=r.clinic order by r.requests_id desc, date desc limit 15,15

and

select r.*, u.name as clinic from requests r, user u where u.user_id=r.clinic order by r.requests_id desc, last_name asc limit 15,15

Where requests_id is a an auto incrementing unique, but this just seems to cause the results not to be sorted at all.

How can I sort just a 15,15 limit AFTER the whole result been sorted by some other means, so I can paginate and then sort on demand?

I may not be completely understanding what you are doing, but it seems to me that you can do this with a subquery:

SELECT * FROM (
select r.*, u.name as clinic from requests r, user u where u.user_id=r.clinic order by date desc limit 15,15 
) ORDER BY last_name ASC;

This probably isn't the best solution but I think it will work.

Actually, it turns out that my basic premise was not very intuitive for a user. We decided that if the user was on a sub-page, and decided to sort a column, it would return to page#1 and sort the entire record, as normal. This would allow the logical browsing of an entire sorted result, which I think is the right thing to do.

Thanks for helping, though. My SQL skills are not where they should be.