custom ORDER BY

BEFOR

My situation is that I have a directory with (3) style plans:

'TYPE I' 'TYPE II' 'TYPE III'

TYPE I and TYPE II should come up on the page, in the same order, under the same 1st positioning, before TYPE III

These should show on the page as follows:

TYPE I or TYPE II
TYPE III

What differentiates TYPEI from TYPEII are other aspects other than the order that they show on the page. There are unrelated benefits aside from order of results.

(so I can't simply insert 1,2,3, into columns respectively)

What values could I insert into the columns and how would I order the query:

ORDER col BY DESC //?

bretticus

Perhaps you could explain this a little better. It's late and I need to go to bed and all, so that may be the main reason I'm scratching my head a little after reading your post ;-)

Maybe you could post the table structure and give some column and row examples.

BEFOR

Query results are being echoed in a do-while repeat region. For the types of plans a user is assigned to, values are deposited in table columns ///1,2,3 etc

Type I and Type II plans should come up in the same order, first, using the ORDER BY clause. Type III simply follows these.

(other factors differentiate the two plans, but I'm only talking about the ORDER BY here, the order these are displayed on the page from select queries)

my question is:

a) what types of values could be used to make sure the two types of plans come up in the same order, with ORDER BY // would alphanumeric for example "3" & "3a", come up in the same order with ORDER BY DESC ? (though this would not be the best solution I realize- - running other queries that are based on numeric values)

b) the ORDER BY clause itself, is there a way it could be written so that two different values for example "3" or "4" could come up in the same 1st order on the page. Above Type III ?

My question is how best to structure this.

nemonoman

I'm with Brettious. Your fuller explanation isn't.
Try showing:
Table structure
Typical data
Desired results

BEFOR

Here we go:

'MplanT'
MpID | M_ID | Mplan |

MpID – primary
M_ID – foreign to 'Member' main table/Member.MemID is primary here for joining
Mplan – contains values for plan type.

Typical values: '2','4','6' that represent TypeI, TypeII, TypeIII

note: currently offering TypeI only //converting, now adding plans Type II Type III – leaving intential gaps for future plans//

The query is a little complicated with joined tables and their explanations and SELECT MAX's, I beleive this is the relevant portion as it ends off:

INNER JOIN MplanT AS plan1 ON Member.MemID = plan1.M_ID AND plan1.MpID = ( SELECT max( MpID ) FROM MplanT WHERE MplanT.Mm_ID = Member.MemID )
…ORDER BY plan1.Mplan DESC

DESIRED RESULTS:

prioritize order of TypeI, TypeII, TypeIII within a do-while, like:

Type II or Type III // in order of first and equal priority

followed by:

Type I // last

nemonoman

If Type1 == 2
Type2 ==4
Type3 == 6

Then you can create an order by like this:

ORDER BY plan1.Mplan IN (4,6) DESC, plan1.Mplan

Means:

if mplan == 4 or mplan == 6 return 1
if mplan == 2 return 0

You want the returned 1s at the top, so order these desc

This puts all 4s and 6s first in a jumble, then the 2s

Now you do a secondary sort on mplan

The secondary sort sorts the 4 and 6 jumble: all the 4s to the top, all 6s below
Then secondary sort sorts the 2s -- pretty easy, since all the 2s are already ordered by 2

BEFOR

don't know if I need the secondary sort, the 4's and 6's have equal priority so jumble may be best.

thanks, appreciate it.