db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dan Scott" <deni...@gmail.com>
Subject Re: order by
Date Tue, 28 Feb 2006 03:01:08 GMT
Sure, a space before either the lastname or the firstname could be the
issue. It's completely unclear whether the original poster was pasting
the exact results of his tests, or just approximating the output. HTML
or post-database processing (such as ltrim() calls) could alter the
output as well.

I just ran a simple test on 10.1.2.1 and it worked exactly as expected:

ij> connect 'jdbc:derby:foofoo;create=true;';
ij> create table names (firstname varchar(12), lastname varchar(12));
0 rows inserted/updated/deleted
ij> create index denada on names (lastname, firstname);
0 rows inserted/updated/deleted
ij> insert into names (firstname, lastname) values ('Michael',
'Miller'), ('Amy', 'Miller'), ('Body', 'Miller');
3 rows inserted/updated/deleted
ij> select * from names order by lastname, firstname;
FIRSTNAME   |LASTNAME
-------------------------
Amy         |Miller
Body        |Miller
Michael     |Miller
ij>  insert into names values (' Sarah', 'Miller');
1 row inserted/updated/deleted
ij> select * from names order by lastname, firstname;
FIRSTNAME   |LASTNAME
-------------------------
 Sarah      |Miller
Amy         |Miller
Body        |Miller
Michael     |Miller

Dan

On 2/27/06, mark boylan <markboylan@hotmail.com> wrote:
> That sounds like a good guess, but since the index & ordering are
> lastname-firstname,
> I'd look for " Miller" and "Miller". But in the email, both do have 4 spaces
> before the last name.
>
> >
> >Is there any chance that the value "Michael" was inserted with a
> >preceding space or some other hard-to-see character?
> >
> >" Michael" comes before "Amy" in most collating sequences.
> >
> >Dan
> >
> >On 2/27/06, Thomas Vatter <thomas.vatter@network-inventory.de> wrote:
> > >
> > > I am too stupid to write an sql 'order by' clause. For 'select
> > > firstname, lastname ...' I have added 'order by lastname, firstname' and
> > > have created a composite index on lastname and firstname. I thought the
> > > result would be an ordering like
> > > Amy    Miller
> > > Bode    Miller
> > > Michael    Miller
> > > Daren    Ralphs
> > > Steven    Ralphs
> > >
> > > But what I get is
> > > Michael    Miller
> > > Amy    Miller
> > > Bode    Miller
> > > Daren    Ralphs
> > > Steven    Ralphs
> > >
> > > I. e. Micheal is ordered as if it was part of the lastname. I don't
> > > understand that. Can anybody help me out?
> > >
> > > tom
> > >
> > >
>
>
>

Mime
View raw message