Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-11-06
14
Medium Priority
?
264 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
11 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 672 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 664 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 664 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Simple Linear Regression
Loops Section Overview

877 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