Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 26 Jan 2008 @ 11:13:05 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Sorting of a Select
From:   Dieter Noeth

Joerg Mutsch wrote:

  It seems that the result set will have the order of the Hashrow Number or the order of insertion (actually I have not Idea which order is used). The same result will appear if I am doing a simple select * from table in Queryman.  

Of course, all data in Teradata is sorted by Rowid within table.

  I can do a sort afterwards but this is kind of annoying. So I would like to order the result set from the beginning as shown in the first option.  

Argh, adding a "order by 1" is too annoying?

  Unfortunately Teradata does not allow to do a sort in the view (this would be too easy).  

Within Relational Model any set of rows is unordered.

  Does anyone have an idea how I can force the system to do the sort as I want to have, without any interaction on the front end tool?  

     replace view "table_without_order_by" as
     select calendar_date
     from sys_calendar.calendar
     where calendar_date between current_date - 10 and current_date
     qualify row_number() over (order by calendar_date) > 0;

     select * from "table_without_order_by";

Or do it the good old MS SQL Server way:

     replace view "Used to work in MS SQL Server 2000" as
     select top 100 percent calendar_date
     from sys_calendar.calendar
     where calendar_date between current_date - 10 and current_date
     order by 1;

     select * from "Used to work in MS SQL Server 2000";

Another solution, if the table is small (because all rows will be stored on a single AMP) returning in order of insertion:

     create table sorted(
        dummy int not null default 42 check (dummy = 42),
        MonthId dec(6,0));

     insert into sorted(MonthId) values(200501);
     insert into sorted(MonthId) values(200502);
     insert into sorted(MonthId) values(200503);
     insert into sorted(MonthId) values(200504);
     insert into sorted(MonthId) values(200505);
     insert into sorted(MonthId) values(200506);
     insert into sorted(MonthId) values(200507);

     replace view "my frontend is limited" as
     select MonthId
     from sorted
     where dummy = 42;

     select * from "my frontend is limited";

Btw, all those solutions still don't guarantee a properly sorted output in every case, only a "select * from xx order by 1" will do that.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020