[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

*Subject*: Re: sorting invoice numbers by numerical order*From*: Luke <..hidden..>*Date*: Tue, 16 Feb 2010 17:23:53 -0500 (EST)

On Tue, 16 Feb 2010, Chris Travers wrote:

On Mon, Feb 15, 2010 at 9:58 PM, Bob Miller <..hidden..> wrote:There was some chat a while ago with regards to sorting by description, and the two suggestions were to sort the query in the sql statement before producing the html page, and the other was to use javascripting to do the same. My question is about numbers, though, not letters. For my installations, when sorting by invoice number, my numbers go like this: 1 10 100 101 102 103 104 105 106 107 108 109 11 110 111 and so on. Is there a way to make it so invoices 11-99 comes *before* 100??If all invoice numbers include only numbers instead of letters, you could alter the SQL query to order by the invoice number converted to a numeric datatype. The problem though is that invoice numbers can include letters, so consequently they are sorted in the db as text strings. Otherwise an invoice like 110-V would cause such a query to error.

Does psql have anything along the lines of the MySQL convert builtin? If so, running the following probably works: select ... order by convert(invnumber as unsigned) asc; In MySQL, at least, that properly sorts: 2 1 10 9C into: 1 2 9C 10

Luke

**Follow-Ups**:**Re: sorting invoice numbers by numerical order***From:*Chris Travers

**References**:**sorting invoice numbers by numerical order***From:*Bob Miller

**Re: sorting invoice numbers by numerical order***From:*Chris Travers

- Prev by Date:
**Re: sorting invoice numbers by numerical order** - Next by Date:
**Re: sorting invoice numbers by numerical order** - Previous by thread:
**Re: sorting invoice numbers by numerical order** - Next by thread:
**Re: sorting invoice numbers by numerical order** - Index(es):