Triple-sorting a result set

Not getting the results I'm expecting

[neophyte]

Hello All -

I've never had to do this before, and I'm a little stuck.

I've got a result set that I need sorted by the 0 or 1 contents of two different fields.

The result is SUPPOSE TO BE that those records with '0' in fld_approvedBy are suppose to be at the top of my table row output, followed by records with '0' in fld_deployed, followed by records with a '1' in fld_deployed.

So, un-approved records lead the list, followed by un-deployed records, followed by deployed records.

What I'm getting (my ORDER BY command shown below) however, is just the opposite: deployed records first, followed by un-deployed records, followed by un-approved records.

ORDER BY fld_approvedBy = '" . 0 . "', fld_deployed = '" . 0 . "', fld_deployed = '" . 1 . "'

I've tried different combinations of ORDER BY but still have not succeeded in my goal. Can someone tell me where I'm going wrong?

Neophyte

[venti]

Im having a little trouble following but if I follow correctly try this:

SELECT *
FROM mytable
WHERE fld_approvedBy = 0
AND (fld_deployed = 0 OR fld_deployed = 1)
ORDER BY fld_approvedBy ASC, fld_deployed ASC

[neophyte]

Hi venti -

Thanks for your input and sorry that I'm being unclear. Tried your solution but it didn't completely solve the issue (deployed crew members were ending up between non-approved crew). If I took out the parens "()" then it ALMOST worked but non-deployed crew ended up at the bottom of the list instead of between non-approved and deployed. I asked my client if that would do: "Nope, gotta be non-approved, non-deployed, deployed". Okay, okay.

Anyway - to start off with - ALL records are either approved or not (1 or 0). That being the case, the first thing I want to do is get the result set sorted to show all non-approved records (0) first (at the top): ORDER BY fld_approvedBy = 0;

Since all remaining records will be approved, I want those records sorted by deployed (1) or un-deployed (0) So, following all un-approved records, I want to show all records with a deployment status of 0: fld_deployed = 0;

Lastly, I want to show all records with a deployment status of 1: fld_deployed = 1;

Sooooo, to my simple mind, I though that:
ORDER BY fld_approvedBy = 0, fld_deployed = 0, fld_deployed = 1
would work just fine as I thought this would order the set as per the order of the ORDER BY clause: put all non-approved crew first, followed by non-deployed crew, followed by deployed crew.

Doing it "my way" completely reverses the sort order: deployed crew are first, followed by non-deployed crew, followed by non-approved crew.

I've tried switching the field order within the ORDER BY clause, but without success.

I hope the foregoing makes things clearer and thanks so much for giving this a go!

Neophyte

[LifeinAsia]

As I understand you, this should work:

SELECT *
FROM mytable
ORDER BY fld_approvedBy, fld_deployed

[neophyte]

LifeinAsia -

That worked PERFECTLY. I'm scratching my head over this (as one does when they've been sweating a problem for scores-of-hours and it's solved so simply. Can you take a moment to explain WHY this works? I was under the (apparently mistaken) impression that you had to be so much more specific (i.e. fld_approvedBy = 0; fld_deployed = 0; fld_deployed = 1) to accomplish what I consider to be a tri-level sort.

Appreciate the solution, and any additional insight if you've got the time to offer it.

Neophyte

[LifeinAsia]

The situation makes it easy- if it's not approved, then it can't be deployed. If it is approved, it can be deployed or not. So you basically only have 3 situations:
A: unapproved, undeployed
B: approved, undeployed
C: approved, deployed
Or, in terms of fld_approvedBy, fld_deployed:
A: 0,0
B: 1,0
C: 1,1
So just sort accordingly.

Another way to look at it:
Instead of a "three-way" sorting, it's actually only two. First, you sort on whether it's approved or not, then sort by whether it's deployed or not.

Now, if for some reason you could have a situation where something was deployed without being approved (e.g., 1,0 in fld_approvedBy, fld_deployed "notation"), and you wanted to exclude those from your results, then you would have to add some conditionals to the SELECT statement.

Or to make it more difficult, if you wanted to sort the unapproved ones differently than the approved ones (i.e., deployed then undeployed for unapproved, but unepeloyed then deployed for approved), then it would be a lot more complicated.

[edited by: LifeinAsia at 4:07 pm (utc) on June 12, 2008]

[neophyte]

LifeinAsia -

Thanks very much for your explanation - I sometimes have a knack of making things way more complicated than necessary and appreciate you taking the time to clear the mists for me!

Neophyte