Sorting behavior is only defined for the columns in the ORDER BY clause.
If a column is not included in the ORDER BY clause, then its sort order
can be arbitrary and not even consistent across executions of the query.
Hope this helps,
-Rick
On 4/27/22 4:35 AM, John English wrote:
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,
