Solved

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

Posted on 2003-11-06
14
253 Views
Last Modified: 2010-04-17
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?
0
Comment
Question by:Smoerble
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 168 total points
ID: 9695561
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
 

Assisted Solution

by:vbexpert
vbexpert earned 166 total points
ID: 9695940
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
 
LVL 3

Assisted Solution

by:merphle
merphle earned 166 total points
ID: 9696528
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 101

Expert Comment

by:mlmcc
ID: 9697936
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
 
LVL 3

Expert Comment

by:merphle
ID: 9698404
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
 
LVL 3

Expert Comment

by:merphle
ID: 9698409
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 9698420
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
 
LVL 3

Expert Comment

by:merphle
ID: 9700350
Yep, it will. As long as you don't reorder the results later.
0
 

Expert Comment

by:AW1712
ID: 9700925
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 9705737
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 10319372
It would be interesting to get some feedback.

Probably solved the problem between all the comments.
Split ?

mlmcc
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When there is a disconnect between the intentions of their creator and the recipient, when algorithms go awry, they can have disastrous consequences.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Introduction to Processes
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question