MySQL: How to sort by 1) a='true' and 2) a<> 'true' ? (Jet SQL) (Urgent)

Hi all,
I don't know if this is a Microsoft-Access specific question or if you can solve it with normal MySQL too?

I need to make an "ORDER BY" cause which sorty a text field. The text field may contain nothing at all (null?), the text "true" or the text "false"  (yes, instead of a boolean, this can not be changed).

Question:
I need a result where all entries with "fieldName='true'" are listed on top and then everything else sorted by the date in "fieldDate".

I tried
ORDER BY (fieldName ='true'), (fieldName <> 'true'), fieldDate

Any ideas?
SmoerbleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Sorting is done by columns.  The first column listed is the primary sort field.  If other columns are added they become secondary, tertiary, etc sort fields.

If 2 or more rows have the same value then the secondary then tertiary columns are used.

Other than writing your own sort routine to do what you want I can't think of any way to do that.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbexpertCommented:
Hi!! Smoerble

       I think you want data with fieldname='True' and you want to sort data based on date. If that's right then you can write a query as

Select * from tblName where fieldname='true' ORDER BY fieldName,fielddate ASC

Regards,

Mihir
0
merphleCommented:
Smoerble,

If fieldName is limited to only those three values (null, 'true', 'false'), you can take advantage of that knowledge, via:

SELECT * FROM tblName ORDER BY fieldName DESC, fieldDate ASC

That will return all rows where fieldName is 'true', followed by rows where fieldName is 'false', followed by rows where fieldName is null. Within those three sections, the rows will be ordered by fieldDate, in ascending order.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlmccCommented:
Unfortunately he wants it (if I read the wuestion correctly) sorted as

rec1 TRUE  date
rec2 TRUE date
rec3 null  10/jul/2003
rec4 FALSE 11/jul/2003
rec5 null  12/jul/2003

etc

All the trues then the rest sorted by date

mlmcc
0
merphleCommented:
Thanks for the clarification, mlmcc.

Smoerble, try this on for size:

SELECT * FROM tblName WHERE fieldName = 'true'
UNION ALL
SELECT * FROM tblName WHERE fieldName <> 'true' ORDER BY fieldDate ASC

Note that you may want to convert fieldName to lowercase before comparing, unless you can be certain of the case stored in the table.
0
merphleCommented:
Sorry, we should probably explicitly test for null, just to be sure:

SELECT * FROM tblName WHERE fieldName = 'true' and not isNull(fieldName)
UNION ALL
SELECT * FROM tblName WHERE fieldName <> 'true' or isNull(fieldName) ORDER BY fieldDate ASC
0
mlmccCommented:
Will union maintain the order ie in this case the TRUEs first then the others?

I thought about that as a solution but didn't think it would keep the order.

mlmcc
0
merphleCommented:
Yep, it will. As long as you don't reorder the results later.
0
AW1712Commented:
I guess the below query would solve your problem.

Select *,
SortedField =
Case when fieldName='True' then 'A'
Case  when fieldName='False' then 'B'
else 'C'
End
order by SortedField ,DateField
0
mlmccCommented:
I think he wants the TRUE then the rest so

Select *,
SortedField =
Case when ((fieldName='True') and (not (isnull(fieldname)))) then 'A'

else 'B'
End
order by SortedField ,DateField

mlmcc

0
mlmccCommented:
It would be interesting to get some feedback.

Probably solved the problem between all the comments.
Split ?

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.