Order By Multiple Columns "Merged"

[webBoi]

I have a MySQL database table with columns:

FirstName (varchar)
LastName (varchar)
Organization (varchar)
RecordType (P=personal,O=organizational)

Some of the records in it are just organizations that don't have first or last names associated. Other records are just people with no organization. In search results, I want to offer a "display name" column that will be either the full name or organization name (another field denotes the record type). But I need to be able to order by EITHER column, depending on which is the record type.

So my query might look like:

SELECT *
FROM MyTable
WHERE [search criteria]
ORDER BY [LastName ASC,FirstName ASC ---OR--- Organization ASC... depending on value of RecordType for this particular record]

Does this make sense? Anybody know how this can be accomplished?

[djr33]

There are a couple ways to approach this.
You could do a fake temporary merge (generate a temporary table during the query) that will then allow you to put those two columns together and use them, but this is messy and I find it hard to work with because you don't ever see what's going on.

You could fix it entirely in the PHP (or ASP, etc.) by predicting which one you will get, or by doing two different queries and resorting. In PHP this is actually very simple: loop through all the results and put them in an array with the key being the column name. Merge the two result arrays (one for names and one for organization), then just use ksort() (sort array by key).


The other way to do this is actually very simple, but I'm not sure it would be entirely reliable depending on what the data actually ends up like. For example, it might behave strangely if both organization and name were set.
But here's the basic answer:
Query: ORDER BY [A, B]
Where A is name and B is organization.

You already have that in your syntax with [Lastname, Firstname], and the same will apply to the third, just add Organization as a third sort by item.

The way this will work:
1. Everything will be sorted by last name.
--1b. If last name blank, then all of those results will be grouped together.
2. Everything will be sorted by first name, within last name groupings.
--2b. If first name blank, then all of those results will be grouped together, and the order should remain the same from sort by last name.
-----2c. If both first and last name blank, then you should at this point have a remaining set that are ready to be sorted by organization.
3. Everything will be sorted by organization, within previous groupings.
--3b. Since the previous groupings separated out the ones that don't have names attached, you will then get them sorted by organization.

However, this will result in a long list of two parts: first those with names, then those with organizations. They won't be mixed in together (that is, it won't look like "Jane Doe, Jim's Pizza, John Doe", but instead "Jane Doe, John Doe, Jim's Pizza").
If you need them mixed together, then you'll need to try one of the more complex methods above.