Re: Sort in Query
Date: Thu, 3 Jan 2008 13:01:13 -0800 (PST)
Message-ID: <0919191f-2a4e-4762-a780-efd75fa281c0@e6g2000prf.googlegroups.com>
Comments embedded.
On Jan 3, 9:16 am, Bram Mariën <b..._at_nospam.abiware.be> wrote:
> On Thu, 03 Jan 2008 05:55:47 -0800, fitzjarr..._at_cox.net wrote:
> >> Bram
>
> > You need to look at an ASCII table and see the character codes
> > involved and the 'logic' may then reveal itself to you; as defined a
> > '/' is a decimal 047, and a '0' is a decimal 048. A '/', therefore,
> > will always sort BEFORE a '0' in a text field (remember you're now
> > using character strings, not numeric values, for your invoice
> > number).
>
> David,
>
> I understand that logic, but why is there a difference in the sort (both
> ASCII I assume) in the resultset of a query without a whereclause and the
> resultset of a query with a whereclause (INV_NR>'somevalue')
>
There is no difference in the sort, as there is none by default. There is no 'default' ordering nor sorting of data without an explicit ORDER BY (with the exception, in 9iR2 and earlier releases, of GROUP BY queries, which is a different discussion).
> If you do :
> WHERE INV_NR>'3307/0002' ORDER BY INV_NR
> I would assume Oracle returns me invoice_no 3307/0003, and not 33070001,
> because / < 0 ?
>
Oracle is using an ASCII collation when applicable, and that's usually on UNIX and Windows machines, so the comparison is done on the ASCII codes for those characters, not the characters themselves. The output of that query is as follows:
SQL> create table invoice(inv_nr varchar2(20));
Table created.
SQL>
SQL> insert all
2 into invoice
3 values('33070001')
4 into invoice
5 values('3307/0002')
6 into invoice
7 values('3307/0003')
8 into invoice
9 values('33070005')
10 into invoice
11 values('3307/0011')
12 into invoice
13 values('5151/0004')
14 select * from dual;
6 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from invoice
3 WHERE INV_NR>'3307/0002' ORDER BY INV_NR;
INV_NR
3307/0003
3307/0011
33070001
33070005
5151/0004
SQL> And, yes, the invoice numbers with embedded '/' will display 'first' when the other leading characters match (as in all of your 3307 records) for the exact reason I explained earlier: '/' has a lower ASCII code than '0'. Without the ORDER BY:
SQL> select *
2 from invoice
3 WHERE INV_NR>'3307/0002'
4 /
INV_NR
33070001
3307/0003
33070005
3307/0011
5151/0004
SQL> And without the WHERE clause but with the ORDER BY:
SQL> select *
2 from invoice
3 order by inv_nr
4 /
INV_NR
3307/0002 3307/0003 3307/0011
33070001
33070005
5151/0004
6 rows selected.
SQL> Without any conditions or ordering:
SQL> select *
2 from invoice
3 /
INV_NR
33070001
3307/0002
3307/0003
33070005
3307/0011
5151/0004
6 rows selected.
SQL> If you want ordered data, you need to use ORDER BY, as you cannot guarantee any specific ordering without it. And I'd be looking at an ASCII table (they are plentiful on the internet and can be easily found with google.com) to come to grips with how ASCII text values sort.
> Bram
David Fitzjarrell Received on Thu Jan 03 2008 - 15:01:13 CST