I have a Java method which displays a view as an HTML table. The method
has parameters to specify the sort column (default is the first column
ascending), filters to select specific rows, and so on.
Recently I came across an oddity which I can't figure out. Consider the
following table and view:
create table t1 (
a integer,
b integer,
c integer,
d integer
);
insert into t1 values
(1,2,3,4),
(1,4,5,6),
(2,3,4,5),
(1,3,4,5);
create view v1 as
select a,b,c from t1;
If I select from the view like this:
select * from v1;
this is what I get:
A B C
-----
1 2 3
1 4 5
2 3 4
1 3 4
This is the same as the order of insertion. However, as soon as I sort
on column A, the sort order for column B changes:
select * from v1 order by a;
A B C
-----
1 3 4
1 4 5
1 2 3
2 3 4
B is now shown in the reverse of the order of insertion.
If I add an "order by" to the view:
create view v1 as
select a,b,c from t1 order by a,b;
I get this:
select * from v1;
A B C
-----
1 2 3
1 3 4
1 4 5
2 3 4
select * from v1 order by a;
A B C
-----
1 4 5
1 3 4
1 2 3
2 3 4
Again, column B is reversed.
Since my table display method automatically inserts an "order by" as in
this example, my displayed table always ends up with the second column
in descending order no matter what I do.
Can anyone explain why this is, and what I can do to fix it so that B
comes out sorted in the "natural" order (order of insertion, or as
specified by "order by" in the view)?
TIA,
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
