[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

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.

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