[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sorting

Posted on 2015-01-15
6
Medium Priority
?
98 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
6 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 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 1000 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

612 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