You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2022/04/27 11:35:38 UTC
Strange sorting behaviour
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
Re: Strange sorting behaviour
Posted by Rick Hillegas <ri...@gmail.com>.
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,
Re: Strange sorting behaviour
Posted by John English <jo...@gmail.com>.
On 27/04/2022 17:37, Bryan Pendleton wrote:
> There is no natural order for rows in SQL. If you care about the order
> of the rows in your result, you must always specify an ORDER BY
> clause.
Well, the "default" ordering (no ORDER BY clause) reflects the order of
insertion. It seems odd to me that sorting by one of the columns should
reverse the sort order of another column.
> Instead of putting the ORDER BY in the definition of the view, put the
> ORDER BY on the SELECT statement from the view.
Except I can't, as the ORDER BY in the SELECT is automagically created
by the method which generates the HTML, based on the user's choice of
column and sort order at any given moment. (Howvwer, I only tried
putting ORDERBY in theview to see if it made any difference, and have
removed it when I found it didn't.
So it seems I'm stuck with everything coming out in reverse alphabetical
order if I sort on column 1.
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
Re: Strange sorting behaviour
Posted by Bryan Pendleton <bp...@gmail.com>.
There is no natural order for rows in SQL. If you care about the order
of the rows in your result, you must always specify an ORDER BY
clause.
Instead of putting the ORDER BY in the definition of the view, put the
ORDER BY on the SELECT statement from the view.
That is, don't do:
create view v1 as select a,b,c from t1 order by a,b;
instead do:
create view v1 as select a,b,c from t1;
select a,b,c from v1 order by a,b;
bryan
On Wed, Apr 27, 2022 at 4:35 AM John English <jo...@gmail.com> 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,
> --
> John English
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>