All Forums Database
NewAmigo 27 posts Joined 02/14
19 Feb 2014
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

VandeBergB 182 posts Joined 09/06
19 Feb 2014

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!

Some drink from the fountain of knowledge, others just gargle.

jinli 10 posts Joined 11/12
19 Feb 2014

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.

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
20 Feb 2014

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.

M.Saeed Khurram 544 posts Joined 09/12
20 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

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.
 

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
20 Feb 2014

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 :-)  

Dieter

shavyani 23 posts Joined 03/15
18 Oct 2015

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.032.304.html

 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!

dnoeth 4628 posts Joined 11/04
18 Oct 2015

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 :-)

Dieter

You must sign in to leave a comment.