Re: Strange sorting behaviour
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
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 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 >
Re: Strange sorting behaviour
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,
