[TriLUG] SQL-fu help request

[Matt Flyer via TriLUG]

I have a custom webpage application that acts as an online event log for some local users.  The event entries are stored in a MySQL database.  The events can have a status of OPEN or CLOSED and the date they are created (and closed) is also recorded.  

The users can select the number of entries per page to display, which defaults to 10 and then go through the pages.  I would like to sort the entries first by status (open first) and then by date.  To attempt this, I have used an ORDER BY clause with these two options.

If I select to display all of the entries on one page, they are in the proper order.  If I select less, e.g. 10 out of the total (currently 28), it selects the first 10 entries made, regardless of date and status and sorts them, the second page does the same.  The problem is that there may be, for example, 20 open entries and page 1 shows 8 with two closed and then page 2 shows 7 more with 3 closed, while whet I want is for the open entries to all be on the first pages, followed by the closed.  

It looks like the sorting is happening after the number of entries is selected according to the limit statement rather than before.  I would like to avoid having to select the entire database and paginate it end would rather the SQL do the listing.  Is there a way to do this more advanced or earlier sorting in SQL?

Sent from my iPad