Solved

Sorting

Posted on 2015-01-15
6
91 Views
Last Modified: 2015-01-16
Hi EE
I have a simple problem sorting. I have numbers and characters eg H1.
Currently these numbers range from H1 to H50. If I sort H1 H2  H4 H5 H6 H28 and H30 I get
H1
H2
H28
H30
H4
H5
H6
is there a way to be able to display
H1
H2
H4
H5
H6
H28
H30

Any help appreciated

chestera
0
Comment
Question by:chestera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 total points
ID: 40552833
try this  T is table names and Field1 is field name



SELECT Field1 from T
order by cint(Mid(Field1,2,2) )
0
 

Author Comment

by:chestera
ID: 40552894
Pratima Pharande

Looks good thank you. It's late night here  I will try tomorrow

Alan
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 40553144
The above will work as long as your numeric portion is limited to one or two digits.  

This variation simply ignores the H for sorting, so it will handle any number of digits:

SELECT Field1 FROM T
ORDER BY Clng(Replace([Field1],"H",""))

Open in new window


And more generically, this will ignore the first character (H or anything else):

SELECT Field1 FROM T
ORDER BY Clng(Right([Field1],Len([Field1]) -1)

Open in new window


Also, if you as the developer have any say in naming/numbering conventions like this,  formatting your numbers to be consistent in length will make them automatically sort properly.  You would use leading/padding zeros to make the numeric portions consistent.

For example -  the following, using a single letter prefix followed by three numeric digits will always sort first by letter, and then by number with no SQL gymnastics:

A001
A002
A004
A005
A006
A028
A030
H003
H009
H015
H022
H100
etc...
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 74

Expert Comment

by:Jeffrey Coachman
ID: 40553464
<No Points wanted>
Going even deeper...
If you had even more control over this data.
(For example, you are the developer and you just recently created this table design...)
You could split this field into two, or possibly more, separate fields (The letter portion and the number portion)
Then simply concatenate them back together for "display" purposes.
Then you could always sort by the numeric potion, ...but display the concatenated version.

This might also be useful if you ever needed to add other "segments" to this value...
For example:
H126-A, 400B45, ...etc

Just anther angle to consider in the future...
;-)

JeffCoachman
0
 

Author Comment

by:chestera
ID: 40554268
Jeffrey Coachman
This was going to be my next move and I might adopt. Thank you for the input

Alan
0
 

Author Closing Comment

by:chestera
ID: 40554284
Thank you both for you input a great help and appreciated
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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