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 <john.fore...@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
>
