Link to home
Start Free TrialLog in
Avatar of Jessee
JesseeFlag for Australia

asked on

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
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

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 ?
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
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.
Avatar of Jessee

ASKER

Hey,

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

colPlaylistPos has a text datatype.
Try the following

.CommandText = "SELECT * FROM tblSongs WHERE colPlaylist= ? ORDER BY CASE  WHEN ISNUMERIC(colPlaylistPos) = 1 THEN CONVERT(INT, colPlaylistPos)  ELSE 9999999 END"
Avatar of Jessee

ASKER

Method .execute of object _Command failed.
What database are you using ?
Avatar of Jessee

ASKER

Access
ASKER CERTIFIED SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial