Solved

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

Posted on 2003-11-06
14
236 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SPLUNK REST  API call to Splunk to create and index? 2 103
noX challenge 17 92
groupSum6 challenge 6 77
advertisement module in core php 4 145
This article will show, step by step, how to integrate R code into a R Sweave document
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now