sql sort question 
Author Message
 sql sort question

I was wondering if anyone could tell me sql syntax to sort a text field
with a digit at the end.  In other words my field will typically store
something like 'g394-9912-1', 'g294-9912-2', 'g294-9912-3' etc.  The
field of type text so it works well to sort  when the last digit doesn't
go to two digits.  If it does then 'g394-9912-11' will be before
'g394-9912-2'.  Hope that makes sense.

thanks for any help,
Samud



Tue, 28 May 2002 03:00:00 GMT  
 sql sort question
Haven't tried this myself but you might try

select * from Tablename
 order by Right(fieldname,1)


Quote:
> I was wondering if anyone could tell me sql syntax to sort a text field
> with a digit at the end.  In other words my field will typically store
> something like 'g394-9912-1', 'g294-9912-2', 'g294-9912-3' etc.  The
> field of type text so it works well to sort  when the last digit doesn't
> go to two digits.  If it does then 'g394-9912-11' will be before
> 'g394-9912-2'.  Hope that makes sense.

> thanks for any help,
> Samud



Thu, 30 May 2002 03:00:00 GMT  
 sql sort question


Quote:
> I was wondering if anyone could tell me sql syntax to sort a text field
> with a digit at the end.  In other words my field will typically store
> something like 'g394-9912-1', 'g294-9912-2', 'g294-9912-3' etc.  The
> field of type text so it works well to sort  when the last digit doesn't
> go to two digits.  If it does then 'g394-9912-11' will be before
> 'g394-9912-2'.  Hope that makes sense.
> thanks for any help,
> Samud

Try this:

ORDER BY left(fieldname,9), val(mid(fieldname,11))

Steve.



Fri, 31 May 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Sort order - Edit Filter Sort or Quick Sort

2. Dataview sort problems with SQL Server

3. VB6 - Newbie needs SQL help sorting using dates in Access DB

4. Sorting character data on SQL Server

5. Yargh! Sorting UNION'd SQL query

6. SQL - sorting one recordset by another's values

7. SQL sort on text field

8. SQL and sorting order

9. Sorting Tables by SQL

10. Need help sorting with SQL

11. SQL help with Sorting

12. SQL Double Sort

 

 
Powered by phpBB® Forum Software