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.

Dieter


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

Attachments

Library

Quick Reference