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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
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
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
I thought about that as a solution but didn't think it would keep the order.
mlmcc
Yep, it will. As long as you don't reorder the results later.
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
Select *,
SortedField =
Case when fieldName='True' then 'A'
Case when fieldName='False' then 'B'
else 'C'
End
order by SortedField ,DateField
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
Select *,
SortedField =
Case when ((fieldName='True') and (not (isnull(fieldname)))) then 'A'
else 'B'
End
order by SortedField ,DateField
mlmcc
It would be interesting to get some feedback.
Probably solved the problem between all the comments.
Split ?
mlmcc
Probably solved the problem between all the comments.
Split ?
mlmcc
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