Does standard SQL have Order by clause?
T-SQL, PL/SQL, MySQL all use Order By clause.
Question 1: Does standard SQL have Order by clause?
I hope your answer is no. If no, then I want to see a reference to it somewhere.
Standard SQL is based on the relational model (not to be confused with relationship relating to PKs and FKs)
A relation model in standard SQL is:
Relation (in T-SQL called Table)
--------------------------
ID Name City <-- Heading: set of attributes (in T-SQL called columns)
------- --------- ------------ (There is no order for columns)
100 Jeff New York \
102 Mike Los Angeles > Body: set of distinct tuples (in T-SQL distinct rows)
103 Hans Pasadena / (There is no order for rows) <-- Order By violates this condition
Columns are identified by names and type names.
(The terms field or record do not exist in database, although they are used in this manner. Fields and records their use is correct on the application forms only). I will give you reference for this statement if interested.
Considering: T-SQL is a dialect of standard SQL (as is PL/SQL is for Oracle). Each vendor implements extensions to the standard SQL where an important feature isn’t covered by the standard.
Question2: Is Order By clause in T-SQL an example of such an extension?
Question 1: Does standard SQL have Order by clause?
I hope your answer is no. If no, then I want to see a reference to it somewhere.
Standard SQL is based on the relational model (not to be confused with relationship relating to PKs and FKs)
A relation model in standard SQL is:
Relation (in T-SQL called Table)
--------------------------
ID Name City <-- Heading: set of attributes (in T-SQL called columns)
------- --------- ------------ (There is no order for columns)
100 Jeff New York \
102 Mike Los Angeles > Body: set of distinct tuples (in T-SQL distinct rows)
103 Hans Pasadena / (There is no order for rows) <-- Order By violates this condition
Columns are identified by names and type names.
(The terms field or record do not exist in database, although they are used in this manner. Fields and records their use is correct on the application forms only). I will give you reference for this statement if interested.
Considering: T-SQL is a dialect of standard SQL (as is PL/SQL is for Oracle). Each vendor implements extensions to the standard SQL where an important feature isn’t covered by the standard.
Question2: Is Order By clause in T-SQL an example of such an extension?
bbao
i think it is YES.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, "Order by" is available to Sql and you should know which column you want to put the order.
ASKER
Thank you all for good comments
-------------
Hi Steve,
I appreciate for the time and effort to clarify this. I am convinced order by has been there all along. I will read your post a few times to make sure I got it all. I am studying for exam 70-461 and I came across to the following questions:
In trying to make sense out of these two questions, I decided to post the above question.
Here, Question 1 states "Name two aspects in which T-SQL deviates from the relational model."
So, what is the answer to this question:
Item one: A relation (a Standard SQL term) has a body with a distinct set of tuples. A table (a T-SQL term) doesn’t have to have a key.
(meaning a relation by virtue of being distinct naturally has a key, but because T-SQL allows duplicate rows it doesn't have key). But we all know that, T-SQL add a hidden row number perhaps to deal with this shortcoming.
Item two: Standard SQL uses Order By 1, 3 <-- to sort by column 1 and column 3 (whatever they might be)
Whereas T-SQL uses Order By CustName, CustAddress <-- to sort by column name
I didn't have this level of understanding before. After you corrected me, I think what I have above is correct. I want to run by you to make sure.
Thanks,
Mike
-------------
Hi Steve,
I appreciate for the time and effort to clarify this. I am convinced order by has been there all along. I will read your post a few times to make sure I got it all. I am studying for exam 70-461 and I came across to the following questions:
In trying to make sense out of these two questions, I decided to post the above question.
Here, Question 1 states "Name two aspects in which T-SQL deviates from the relational model."
So, what is the answer to this question:
Item one: A relation (a Standard SQL term) has a body with a distinct set of tuples. A table (a T-SQL term) doesn’t have to have a key.
(meaning a relation by virtue of being distinct naturally has a key, but because T-SQL allows duplicate rows it doesn't have key). But we all know that, T-SQL add a hidden row number perhaps to deal with this shortcoming.
Item two: Standard SQL uses Order By 1, 3 <-- to sort by column 1 and column 3 (whatever they might be)
Whereas T-SQL uses Order By CustName, CustAddress <-- to sort by column name
I didn't have this level of understanding before. After you corrected me, I think what I have above is correct. I want to run by you to make sure.
Thanks,
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
T-SQL, MS Access, MySQL, and PostgreSQL all allow you to use the names of the columns in an ORDER BY clause.
ASKER
Hi Dave,
What you have is 100% correct. But here the discussion pertains comparing a relation (a Standard SQL term) with table (a T-SQL term) not necessarily discussing what is correct in T-SQL.
I don't mean to say your reply is missing the point; not at all. What is happening is the question is evolving into a different direction after reading the comments from Steve.
MIke
What you have is 100% correct. But here the discussion pertains comparing a relation (a Standard SQL term) with table (a T-SQL term) not necessarily discussing what is correct in T-SQL.
I don't mean to say your reply is missing the point; not at all. What is happening is the question is evolving into a different direction after reading the comments from Steve.
MIke
Since I'm not going to pay ANSI for the full documents, I found this page from Oracle http://docs.oracle.com/cd/B12037_01/server.101/b10759/ap_standard_sql001.htm about their compliance to the SQL:2003 standard. It says that statements like CREATE TABLE are in compliance with the SQL standard. This page http://en.wikipedia.org/wiki/Join_%28SQL%29 talks about 'relations' in terms of JOINs.
@Dave
You realize your comment contradicts your quote, right?
You realize your comment contradicts your quote, right?
@kaufmed, I don't know what you're talking about. ??
NOTE: I'm assuming that Standard SQL is ANSI SQL.
Question 1: Does standard SQL have Order by clause?Yes.
Question2: Is Order By clause in T-SQL an example of such an extension?No.
Item one: A relation (a Standard SQL term) has a body with a distinct set of tuples. A table (a T-SQL term) doesn’t have to have a key.Not correct. I think you are confusing with data model design rules.
Item two: Standard SQL uses Order By 1, 3 <-- to sort by column 1 and column 3 (whatever they might be)Also not correct. You can order by column names or by their order in the SELECT columns list in both ANSI and T-SQL.
Whereas T-SQL uses Order By CustName, CustAddress <-- to sort by column name
TracactSQL (aka T-SQL) and PL/SQL are programming languages. They don't process SQL statements at all.
SQL Server and Oracle have SQL engines that do process the SQL statements issued by these programming languages and other clients.
As far as I am aware, ORDER BY is part of the ANSI standard. I have used a lot of different RDBMSs over the years and I haven't seen one that doesn't have an ORDER BY. What I have seen which I am not sure if it is standard or not, is that some RDBMSs allow the use of a columns position in the select list in the ORDER BY clause, for example ORDER BY 1 rather than ORDER BY col1. This may be an extension to the standard.
SQL Server and Oracle have SQL engines that do process the SQL statements issued by these programming languages and other clients.
As far as I am aware, ORDER BY is part of the ANSI standard. I have used a lot of different RDBMSs over the years and I haven't seen one that doesn't have an ORDER BY. What I have seen which I am not sure if it is standard or not, is that some RDBMSs allow the use of a columns position in the select list in the ORDER BY clause, for example ORDER BY 1 rather than ORDER BY col1. This may be an extension to the standard.
To echo Phillip's and my comment in your previous question, this wasn't on the 70-461 exam, so don't sweat it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can't ORDER BY a column that you didn't SELECT.This is not correct. You can order by a column that isn't in the SELECT list. This is only true for GROUP BY.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jim,
I appreciate for generous and useful comments you always add to my questions. I am not quite familiar with all test items you have listed. I just completed MTA 98-364 with 81 points whereas you have don 70-461 with 90 points. This goes to show how good you are in the subject. At this point, I barely could make it to 50 points in 70-461so I have a long way to go.
Thanks again,
Mike
I appreciate for generous and useful comments you always add to my questions. I am not quite familiar with all test items you have listed. I just completed MTA 98-364 with 81 points whereas you have don 70-461 with 90 points. This goes to show how good you are in the subject. At this point, I barely could make it to 50 points in 70-461so I have a long way to go.
Thanks again,
Mike