Solved

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

Posted on 2003-11-06
14
245 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
14 Comments
 
LVL 100

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 100

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 100

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 100

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 100

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

820 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