Printable View
This might be a pretty basic question, I am fairly new to this stuff:
Is there a way to set the ORDER BY to start at a different location than the beginning of the alphabet? I would like to sort the results by a column called 'office' but have the first results start with the letters 'DR' then move through the alphabet ending with rows that have 'DQ' in the 'office' column.
Is there a way to put a formula in the ORDER BY directive?
Thanks anyone who can help.
Is this what you mean?
SELECT office FROM t WHERE office>'DQ' and office<'DS' ORDER by office DESC;
Thanks for the reply, but no, that is not what I am after. That brings up only the results that have 'DR' in the office column, and want to bring all of them up, but list them starting with 'DR' results.
I could sort the result using PHP, but there are usually many pages of results so being able to pass the variables that make up the SQL is much preferable. I was wondering if you can apply a formula to the ORDER BY part of the sql? Or, if there is a much simpler solution I am just overlooking?
I'm not sure I follow - can you provide some sample data and results? If you mean starting at DR, going to DQ, DP etc, then simply:
SELECT office FROM t WHERE office <='DS' ORDER by office DESC;
will work. If you mean there's data DR, DT, DS, DP etc, I don't understand how you mean to sort them starting at DR. Would the results be DR, DP, and then back to DT, DS??
Okay, let me try to clarify. There are 1-2000 records, each one has one of about twenty offices associated with it(office column). I have a search function that generates an sql something like:
"Select from table where price between '$low_price' and '$high_price' and type = '$type' and area LIKE '$area' ORDER BY office, price LIMIT $limit, 10"
So from this query there are results that match the parameters from many different offices, If I leave the query like that the results always start with the office that is the first in alphabetical order. I would like to be able to list the results in alphabetical order by office starting from anywhere in the alphabet, depending on the users preference.
So for instance starting with rows that have an office that begins with 'DR' moving through the alphabet to Z, then back to the beginning finishing up with rows that have offices that start with 'DA' for instance.
Clear as mud?
It's basically going to be in PHP - you can either do the sort from the one result set, or more simply run 2 queries. The first returns from DR to Z, the second from A-DQ (for example). From your examples, something like:
"Select from table where price between '$low_price' and '$high_price' and type = '$type' and office>='$start' and area LIKE '$area' ORDER BY office, price LIMIT $limit, 10"
and
"Select from table where price between '$low_price' and '$high_price' and type = '$type' and office<'$start' and area LIKE '$area' ORDER BY office, price LIMIT $limit, 10"
Okay, so the answer to my original question "Is there a way to set the ORDER BY to start at a different location than the beginning of the alphabet?" is probably no.
I have realized the option of sorting using php, or using two queries, but was hoping for a way to keep it all within one result set due to the ease of keeping track of where you are in the results since I have to generate links to all the pages of results (usually 10 per page).
The other way to do it I suppose would be to get the data out with two queries, then reinsert it into a temporary table with a priority column based on the office value. Part of my problem is not being able to adjust the structure of the tables that are holding the data, I am just able to select from these tables.
Anyway, thanks for your input, if I find what I am looking for I will repost it here.
My question was prompted by this line in the MySQL manual 6.4.1 [ORDER BY {unsigned_integer | col_name | formula } [ASC | DESC] ,...]