[Koha] Fwd: Barcode Sorting Issue in Report

[Ata ur Rehman]

Vali: Plz send your queries on mailing list.  So that others can suggest
some solutions.
---------- Forwarded message ----------
From: Abdul Vali <abdul.vali99 at gmail.com>
Date: Tue, Jan 11, 2011 at 12:24 AM
Subject: Barcode Sorting Issue in Report



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/0ad5245c/attachment.htm