Nevermind... Found some code...

[Ngreth Thergn]

Ok. In the DB we store a Low and a High trivial.

The issue with sorting is I must chose ONE of these to sort by. MySQL simply does not have the power for a sort more complex than that. I cannot say "in this case sort by low in this case sort by high" I must choose ONE to sort from (I can then sub sort off another... but the main sort is already done...)

I know that NONE of these are ideal... Ideal is...

20 <50 75 70<=x<80 >85 90

but I cannot get that.

Now. If someone can come up with a math formula so that ANY combination of low and high trivials can be sorted that way, then I can probably do it... but it better be a single formula. No "if" or other conditional in it.

So I need to vote on what order you would like to see things in.

If I do NOT see at least 100 votes on this poll. Then you are getting what I have made, and ANY posts complaining about the current sort order will get summarily DELETED with no consideration of your post. You are now being given your chance to vote. Do it or deal with it.

<hr>
For those more technical minded...

the trivials are stored in TWO fields... so the list is like this:

[code:1]low high displayed trivial
20 20 20
? 50 &lt;50
75 75 75
70 80 70&lt;=x&lt;80
85 ? >85
90 90 90

[/code:1]

rember only the low and high column is stored.

It looks like some code has been given to me that will be BETTER. Being a computer, it will not be perfect... but it should be ALOT closer to what we desire...

Thank you for all of the sugestions.

[Delores Mulva]

Um....

Which one of those formulas should I put my "X" beside if I want the results sorted by high trivial, lowest to highest? My concern when looking up something is "do I have the skill to do this", not "is this a potential skill path".

Hmm. Is it possible to have two separate searches? One ascending on low trivial, for people looking for skill paths, and one ascending on high trivial, for those seeing if it's safe to press combine? My answers to the previous two polls, putting no-fails at the start of a sort and unknowns at the end, assumed a structure similar to this.

My apologies if this has been covered elsewhere. I saw the House Ogre's demand for votes in the General section and scampered here with all haste before looking anywhere else!

Delores Mulva
Lotus Cult
Quellious

[Taraddar]

I'd vote but i'll be dammed if i can figure out what the heck they mean. Any chance at a little better explaination of the options?

[Morani]

&lt; stands there scratching her head at the idea of being out thunk by an Ogre...>


Sorry there Big Guy, but I too am confused by what each option means. I'd like to give the input you are requesting but I've got NO knowledge of how a database works other than data entry on Excel.

[byelka]

I think this is what he is trying to say


there are 6 recipes some with known triv some without here they are

20
&lt;50
75
70&lt;=x&lt;80
90
>85


So his question is I think order them like this
A
20
&lt;50
75
70&lt;=x&lt;80
90
>85

OR
B
>85
20
&lt;50
75
70&lt;=x&lt;80
90

OR
C
20
70&lt;=x&lt;80
75
>85
90
&lt;50

OR

This one has my vote!
D
&lt;50
20
70&lt;=x&lt;80
75
>85
90

{note from Ngreth: I would LOVE this but MySQL does not allow it}
But I think I would rather see
20
&lt;50
70&lt;=x&lt;80
75
>85
90

At any rate it is kind of a brainteaser to think which is the best :shock:

[wombat]

hmm

Hmm I hope I got the quesion right but I will go with "High" please

[Ngreth Thergn]

Byelka is correct (the polls just do not give me a good way to display it.)

and byelka's 5th choice Is what I want, but is also NOT what MySQL will allow.

[Boleslav]

How about trying to define a field as the average of the high and low trivial, but treating items with only one end point as if that endpoint were the average. It might look something like this:

IF(lo&lt;>"?" AND hi&lt;>"?", (hi+lo)/2, IF(lo="?",hi,lo))

This is a nested IF like you might use in Excel. IF(condition, true part, false part)

This would take the average of the high and low trivials if both existed. In cases with just one endpint, it will take that value.

For the set of data you provided, it would sort them as follows:

Low High Displayed Trivial
20 20 20
? 50 &lt;50 (it will think of it as 50)
75 75 75
70 80 70&lt;=x&lt;=80 (it will think of it as 75)
85 ? >85 (it will think of it as 85)
90 90 90

Boleslav Foregehammer
Paladin of Brell in his 61st Campaign
E'ci – Sacred Destiny

[Twistagain]

The average can be used in SQL like so (assuming Table 1 contains numeric fields Hi and Lo)

SELECT Table1.Hi, Table1.Lo, ([Hi]+[Lo])/2 AS AvgTriv FROM Table1 ORDER BY ([Hi]+[Lo])/2;

[Nicol Heartsbane]

trivial

Heck, make all methods available.

[tsbaity]

Or even better more testing to nail down those range trivials

Yenen
Lanys T'vyl

[Velurian]

In the open-ended ranges (e.g., "> 50" or " &lt; 85"), how exactly are the non-existent endpoints represented in the database? NULLs? Simply empty? Some specific string value?

There's got to be a way to do this the way you (and I) want, I'm just having a bit of trouble wrapping my head around it without knowing how it's implemented...

(/em did this stuff in a previous life, but it's been a while...)

[Magrath]

Found a mysql server, please ignore earlier posted TransactSQL stuff. MySQL doesn't have union, ack. I think this will do what you want:

select (coalesce(low,high)+coalesce(high,low))/2 as nominal from trivs
order by nominal

[Knochi]

The sort order is no biggie after all. As long as it's consistent and some logic behind it people will get used to it.

[Morani]

OOOHH!!!!! Thanks for the explanation. I was trying to read it like a math formula rather than a string. Now I know why I can never get certain things to do what makes sense with Excel too. I have to agree, I like the one that can't be done better. (Why do I always want what I can't have? )

Given the choices, I voted for A but D is a close second.