• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1447
  • Last Modified:

Custom ORDER BY in MS Access

Hello, it is possible to customize the order by from a query? I have one table in MS Access 2007 with a text field. I want to order the result form the query in following way:

1. Field with suffix "_HH"
2. Field with suffix "_H"
3. Field with suffix "_L"
4. Field with suffix "_LL"
5. Other suffix (alt. order by ASC)

Thank you!

SELECT tblVariable.TagName FROM tblVariable
ORDER BY tblVariable.TagName ASC

Open in new window

0
tselectro
Asked:
tselectro
  • 8
  • 7
  • 3
2 Solutions
 
MNelson831Commented:
Try uising a CASE statement in the order by clause:

Order By
     CASE
          WHEN right(FieldName, 2) = 'HH' then 1
          WHEN right(FieldName, 2) = '_H' then 2
          WHEN right(FieldName, 2) = 'LL' then 3
          WHEN right(FieldName, 2) = '_L' then 4
          Else 5
     END

0
 
TextReportCommented:
I would use the SWITCH function like this

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",5,True,5)

Cheers, Andrew

PS Access doesn't support the SQL CASE statement
0
 
GRayLCommented:
How about:

ORDER BY Mid(fldStr,InStrRev(fldStr,"_")),fldStr

MNelson831: Does A2007 handle Case?  Unless it's been changed in A2007, when you provide an integer in the ORDER BY field, Jet picks up the field in the SELECT clause indicated by the number.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
MNelson831Commented:
mea culpa - I jumped here from the SQL Server page and didn't realize the question was in Access.
0
 
GRayLCommented:
To correct Andrew's:

SELECT fldText FROM tblName ORDER BY Switch(Right(fldText,3)="_HH",1,Right(fldText,2)="_H",2Right(fldText,2)="_L",3,Right(fldText,3)="_LL",4,True,5) & fldText;

0
 
MNelson831Commented:
You could also build a seperate table that look slike this this:

TextData                  SortOrder
_HH                             1
_H                               2
_LL                              3
_L                               4


Then left join that table like this:

Select * from MyRegularTable left outer join MySortTable
on Right(MyFieldName, len(MySortTable.TextData)) = MySortTable.TextData
order by nz(MySortTable.SortOrder,99)



0
 
TextReportCommented:
To correct it and to fix the ones missed also by GRayL

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

I think that covers them all.

Cheers, Andrew
0
 
GRayLCommented:
Andrew:  Pls run this simple query in A2007

SELECT anyFld FROM anyTable ORDER BY 1;

using your own field and table names.  Does that work, ie. order by anyFld?

0
 
GRayLCommented:
tselectro:  Did you try my solution at http:#a24042090 ??
0
 
GRayLCommented:
Andrew, I don't have A2007.  Does the use of an integer in the ORDER BY clause call up the relative field from the SELECT clause?

Ray
0
 
TextReportCommented:
GRayL there is an error in the SWITCH(), it's missing a comma after the 2.
Also please note the requested order of HH, _H, _L and LL this is not a standard sort order.
Finall Yes ORDER BY 1 works in Access 2007 as it did in previous versions.
Cheers, Andrew
0
 
GRayLCommented:
Then you realize you were awarded a solution where the asker did not test it.  Your solution would have tried to call up a field, depending on how the Switch() evaluated.  If it rendered a value higher than the number of fields, it will throw an error.
0
 
TextReportCommented:
GRayL, I dont understand what you are getting at. I fixed the issue in the posting that was accepted as an assisted solution. The Switch will carry out 5 tests, the first 4 against the value of the last 2 characters of the field and the 5th being the ELSE as the 9th parameter is True and will return 5.

As to whether it was tested by tselectro then I don't know, as far as I can tell the assisted solution works.

Cheers, Andrew
0
 
GRayLCommented:
your query:

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

Calls up a field named Suffix ?? in the Switch() function.  However Switch() evaluates, one value of 1 thru 5 per record, will result in Jet asking for the first through fifth field from the SELECT clause - for each record.  As you only listed one field, as soon as Switch() evaluates to 2 or higher, it will generate an error - if not before during compile.

That is why I ampersanded the field name to the result of the switch function.  Sorry I copied your missing comma but them's the breaks.  Understand now?

0
 
TextReportCommented:
Suffix is the name of the field, it is checking the RIGHT most 2 characters of the field then same way the CASE example whould have done in SQL Server it is not referencing additional fields, it is just a flat form ofthe nesting of IIF's

I think you may be mixing up SWITCH() in VBA with CHOOSE() in VBA

Cheers, Andrew
0
 
TextReportCommented:
GRayL, just realised what you are getting at with the ORDER BY 2 sorry. No it is a value that will be sorted as a number 2 rather than the column location. Bit slow of me realising what you mean, one eye on the footie.
Cheers, Andrew
0
 
TextReportCommented:
Hi GRayL, a bit more refreshed after too much celebrating of the footie. I have double checked this and as I thought as soon as you put an expression in the ORDER BY you loose the ability to ORDER BY column position for that specific ORDER BY column. This is in Access and as far as I am aware is the same in all other SQL languages that support ORDER BY 1

ORDER BY 1,2 will sort by the first column then the second
ORDER BY 1, IIf(IsNull([Field2]),2,1) will sort sort by the first column then the NOT NULL Field2 Records followed by the NULL Field2 records
ORDER BY IIf(IsNull([Field2]),1,2) DESC, 1 will sort sort by the NOT NULL Field2 Records followed by the NULL Field2 records then the first column.

I hope this clarifies the situation for everyone with regards to the ORDER BY column location.

Cheers, Andrew
0
 
GRayLCommented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now