[Koha] Barcode Sorting Issue in Report

[Abdul Vali]

dear list members

i have created a report in koha3 with the following sql:

SELECT
items.barcode,items.dateaccessioned,items.price,items.itemcallnumber,items.holdingbranch,biblioitems.biblioitemnumber,biblioitems.isbn,biblioitems.publicationyear,biblioitems.pages,biblioitems.size,biblioitems.place,biblio.title,biblio.author,biblio.copyrightdate
FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber) ORDER BY (items.barcode) asc

now items.barcode field has values in alpha numeric (some are starting from
1, 2, 3, 4, etc and some barcodes are with prefix like m23, m45, m46, m67,
etc )  but in my report i got the output in strange sorting order like:

items.barcode
----------------------

1565678
89765
u123456711
u12345679
....
....

but i want to sort numbers first, then barcodes with prefix.  Can someone
guide me how to do so?

a friend of mine suggested me to use CAST in sql but i don't know what is
this and how to use it
-- 
Regards

Abdul Vali
Librarian - ASE School
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20110111/bd0b79fe/attachment.htm