doing subsorts in mySQL

[sfullman]

I'm not usually over on the SQL side of the forum, but I have a rather difficult situation that I could use some SQL language approaches to take a stab at.

I'm creating a feature that allows a user to click the heading of a database-generated table, to determine sort.

Suppose you had a table showing Category, FirstName, and LastName. Clicking LastName will sort by LastName, so the "order by" clause of the SQL query is:

'order by LastName ASC'

Clicking Next on Category will "move over" the LastName sort in the heirarchy so the order clause is now:

order by Category ASC, LastName ASC

HERE'S THE DIFFICULTY:

Suppose you've got hundreds of records, and one John Smith who's a carpenter (category) who is record #700 when the sort is LastName ASC. But, when the sort is Category ASC, LastName ASC, 'ol John has now moved to #300. So trying to say " limit 700, 20" is not going to work -- user gets frustrated because he really wanted carpenters, not plumbers.

I had at least one thought of doing two sql queries, and the first one CONCAT's the two sort indices; the number of sort-sort concats numerically less than our guy John, plus 1, = John's new record number which we can use in the new "limit x, 25 " clause.

Is it possible to do a sort within a sort? Any thoughts or approaches would be welcome on this one.

Sam Fullman
Compass Point Media

[crimaniak]

PHP need mind-reading API to do this.
Or you can do 'carpenter' as a link. This user click it, you set sort by 'Category ASC, LastName ASC' ... AND do the next:

SELECT @MoveTo:=count(*) FROM xxx WHERE Category<'carpenter'

SELECT xxx FROM xxx SORT xxx LIMIT @MoveTo,@PageSize

I don't remember if MySQL can use variables in LIMIT clause by I hope idea is clear.