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

VB6 SQL Order By (Number)

Hey,

Im looking for how to correctly order by a number.
At the moment, im getting results like:
1
10
11
2
3
4
etc...

How do I do it correctly?

Thanks,
Jessee
0
Letsgetcoding
Asked:
Letsgetcoding
  • 5
  • 3
1 Solution
 
DhaestCommented:
How are these numbers stored ? (I assume as a string). In what do you want to sort ?
Can you show us some code where you perform the sorting ?
0
 
DhaestCommented:
If it's on sqlserver, try something like this

SELECT
 ...
ORDER BY
  CASE  
    WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value)  
    ELSE 9999999 -- or something huge
  END,
  value
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Sounds like the field is text and not numeric.  I believe an ORDER BY CAST(DesiredField AS Integer) should work if I'm right... but if you want a more confident answer, you'll have to post some code and explain the datatypes of the fields you're using.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LetsgetcodingAuthor Commented:
Hey,

.CommandText = "SELECT * FROM tblSongs WHERE colPlaylist= ? ORDER BY colPlaylistPos"

colPlaylistPos has a text datatype.
0
 
DhaestCommented:
Try the following

.CommandText = "SELECT * FROM tblSongs WHERE colPlaylist= ? ORDER BY CASE  WHEN ISNUMERIC(colPlaylistPos) = 1 THEN CONVERT(INT, colPlaylistPos)  ELSE 9999999 END"
0
 
LetsgetcodingAuthor Commented:
Method .execute of object _Command failed.
0
 
DhaestCommented:
What database are you using ?
0
 
LetsgetcodingAuthor Commented:
Access
0
 
DhaestCommented:
.CommandText = "SELECT * FROM tblSongs WHERE colPlaylist= ? ORDERBY VAL(colPlaylistPos)  "
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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