Order by Date

Database
Enthusiast

Order by Date

Hello All,

I have created a Date dimension table using sys_calendar.calendar table, but when i use a select statement on my Date_Dim table, the order of the rows is very eratic, is there a way i can change the way it returns the rows..say i want to have it ordered by the date field, i wanted to do it at the table level. 

I am not interested in using the Order by along with the Selct statement. When i use the Select * from Date_dim, i wanted to see the retrieved rows default sorted by the date.

Many Thanks

Reddy

9 REPLIES
Enthusiast

Re: Order by Date

One of the tenets in basic relational theory is that order of the columns and rows in a table is irrelevant.  If your results come out in the order you'd like them to it's probably dumb luck. 

A select * query is be default an all amps query, huge data skew notwithstanding, and the order of the rows is again irrelevant, you've asked the db to give you all the rows without any qualifications on how their ordered.

Without the order by clause, there's no guarantee what order the rows will be presented. 

FWIW, the order by clause doesn't go to work until after the resultset is complete.

Thanks!

Enthusiast

Re: Order by Date

sounds like OP expects an oracle IOT-way in TD, but you might need to know first the fundamental HUGE difference between share-nothing MPP design in TD and share-everything design in oracle. i'd like to say - enjoy the "order by" along with "select" in TD. TD is not oracle in almost any way.

Teradata Employee

Re: Order by Date

Thats true, and we have many discussions on the forum relating to comparison between Teradata and Oracle/other RDBMS.

But you can surely get all the DWH needs fulfilled by simple or complex solutions.

Enthusiast

Re: Order by Date

Hello Adeel, i am trying to create a view from your suggestion with SQL below

CREATE VIEW VW_TEST

(calendar_date, year_of_calendar, quarter_of_year)

As

Select calendar_date

, year_of_calendar

, quarter_of_year

From DB.BW_TEST

ORDER BY calendar_date;

But its coming as error, ORDERBY is not allowed in Subqueries, can you please hlep.

I ahve tried putting the ORDERBY before and after the From statement, but its not working.

Enthusiast

Re: Order by Date

AFAIK, You can not use Order by inside a view, You can only use Order by within a view with last select of a set operation e.g Union

Create view Test

(

SELECT....

UNION

SELECT

Order by 1,2,3...

)

Khurram
Teradata Employee

Re: Order by Date

My bad Reddy .... yes you can't have an order by in a view.

Having it in a MACRO is possible, but i believe it wont be very useful in your scenario as you cant use a MACRO in SELECT.

Junior Contributor

Re: Order by Date

Hi Reddy,

ORDER BY within a view is not allowed.

There's a workaround, but it's definitely not recommended: Add TOP.

REPLACE VIEW xxx AS SELECT TOP 100 PERCENT * FROM tab ORDER BY col;

But there's still no guarantee that a SELECT * FROM xxx will return a sorted result.

The main question is: why do you think you need to do this?

What's so complicated in adding ORDER BY?

Btw, in MS SQL Server people used to do the TOP 100 PERCENT to get sorted output and when the optiizer was enhanced to eliminate that unnneccessary sort they complained that their application fails. Later they started using TOP 99.99999999999 PERCENT instead :-)  

Enthusiast

Re: Order by Date

Hello ,

we have been developing reports , we have a "Select query with multiple joins and columns , retrieved from a report , but when we run the same query  in teradata the sort differs with the original report sort  though we use the same "order by" clause that we use in the report... This has been a never ending issue with "Order By". I just also want to make sure

1.If casting of dates from its original format  to a  varchar date would be a reason for this sort variation...

2.one more observation is if the columns contributing to the "Order By" clause has same data then the data is being sorted on the some other column , on what basis is this column chosen. 

I have ensured it meets all expectations for "order by " defined here 

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch01...



 This sort issue should be resolved asap ,Please share your thoughts as this is a very wierd issue we have been facing... 

Any help is greatly appreciated. 

Thanks in Advance!

Junior Contributor

Re: Order by Date

I never encountered problems with sort orderm but without showing both "order by" it's hard to tell...

Q1: Of course casting a date to string might change the order if it's not based on yyyymmdd. 

Q2: if I understand correctly you do something like this:

SELECT
CAST(datecol AS CHAR) AS datecol
...
ORDER BY datecol

In this case you order by the column datecol, not the alias.

Now if this is created as a view and you run 

SELECT *
FROM my view
ORDER BY datecol

you order by the alias datecol :-)