Solved

Sorting

Posted on 2015-01-15
6
95 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

635 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