[thelist] Sorting with SQL

[Carl Edwards]

I had to solve a similar problem with part
numbers which sorted asc looked like:
   VSC055
   VSC1129
   VSC120
   SSC100

SELECT
   product.number,
   CONCAT(0, SUBSTRING(product.number, 4, 4))+0 AS num,
   LEFT(product.number, 3) AS prefix
FROM
   product
ORDER BY
   prefix,
   num

Get's them sorted like:
   SSC100
   VSC055
   VSC120
   VSC1129

I hope you can adapt this technique for your data,
-Carl Edwards


> -----Original Message-----
> From: Rob Smith [mailto:rob.smith at thermon.com]
> Sent: Tuesday, September 23, 2003 11:38 AM
> To: Thelist (E-mail)
> Subject: [thelist] Sorting with SQL
> 
> 
> Hi list,
> 
> I've got a huge database and I need to display products in 
> ascending order.
> What I need is:
> 
>   ABC 1-2
>   ABC 3-6
>   ABC 4-5
>   ABC 10-3
> 
> However when I sort by ascending order I get
> 
>   ABC 10-3
>   ABC 1-2
>   ABC 3-6
>   ABC 4-5
> 
> Is there a trick I can do to sort these (about 30) records in 
> the "correct" order.
> 
> Rob Smith
> --