Solved

Does standard SQL have Order by clause?

Posted on 2015-02-01
19
301 Views
Last Modified: 2015-02-02
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?
0
Comment
Question by:Mike Eghtebas
  • 4
  • 3
  • 3
  • +7
19 Comments
 
LVL 37

Expert Comment

by:Bing CISM / CISSP
Comment Utility
i think it is YES.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
Comment Utility
I think you may be misunderstanding the statement "There is no order for rows".

In a relational database the default is that there is no order to the rows returned from a query.

So unless you're fetching along a clustered index in SQL Server (which by definition is the physical order of the pages on disk in the order defined on the clustered index - which is a SQL Server extension), there is no guarantee of the order of the rows returned by "select col1, col2 from table".

I have tested this in the past in an Oracle database and for a large set, I can run a query (I even forced a hint on a non unique secondary index where duplicates are allowed), the order of the rows returned varied from query to query.

The purpose of the order by clause is to allow you to sort the rows returned by a query into a meaningful sequence.

The order in the RELATION is not fixed (again, except for a clustered index), but the results of the QUERY of the relation can be ordered.

I'm not searching all the parts of the SQL ANSI standard for you - but there are various articles on wikipedia linking to the  8 variants of the SQL ANSI Standard as it has evolved over the last 30 years.

See: http://en.wikipedia.org/wiki/SQL (look under "Dialects")

I bet if you look, you'll find that ORDER BY is a standard part of the SQL Language (I remember learning it in college when I first saw SQL in 1985 / 86... 30 years ago).

So that would mean that your question 2 is irrelevant, since I believe that ORDER BY is a part of the ANSI standard.

EDIT: I found this: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt  which appears to be a review draft of the SQL 1992 ANSI revised standard.  The ORDER BY clause is mentioned frequently in this document - look at page 371 (Data Manipulation) - heavily referenced there and elsewhere in the document.

If it weren't a part of the ANSI standard, why would it be so heavily referenced in the standards document ?

Each RDBMS Vendor is going to do what they want anyway ... adding to or not implementing part of the "Standard" - but from your question, ORDER BY seems to always have been a part of the core of the language.
0
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
Yes, "Order by" is available to Sql and you should know which column you want to put the order.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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:QuickCheck
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
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 25 total points
Comment Utility
ORDER BY must specify a column or expression in the Result set.  You can't ORDER BY a column that you didn't SELECT.  From https://msdn.microsoft.com/en-us/library/ms188385.aspx :
The column names referenced in the ORDER BY clause must correspond to either a column in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.
I believe MySQL has the same limits.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
T-SQL, MS Access, MySQL, and PostgreSQL all allow you to use the names of the columns in an ORDER BY clause.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
@Dave

You realize your comment contradicts your quote, right?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
@kaufmed, I don't know what you're talking about.  ??
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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)
                           Whereas T-SQL uses Order By CustName, CustAddress    <-- to sort by column name
Also not correct. You can order by column names or by their order in the SELECT columns list in both ANSI and T-SQL.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
To echo Phillip's and my comment in your previous question, this wasn't on the 70-461 exam, so don't sweat it.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
Comment Utility
My notes on the 70-461 exam
I ended up buying the MS Press 70-461 book off of eBay for $30, and took the practice exam with only a breezing of the book. Got a 90%.

Unless I'm missing something, the practice exam was way too easy: nothing on XML, nothing on indexing / stats, execution plans, very little DMO, nothing on the new 2012 stuff such as TRY_CAST or OFFSET, no JOINs other than EXCEPT-INTERSECT-UNION. This mostly matches up with your comments below on the test.

The only Q's I really missed was on transaction isolation levels, which I've never had to deal with.

Notes from a buddy that passed
It wasn't too bad. There were a few sections that I got stuck on but overall it wasn't too bad. I got a 906. Here is a quick rundown that I sent to some guys I work with.

I passed the test today so here is a rundown of what was on it.

I had 44 questions.

4 were on XML. The list of answers were the same for each question. The answers were either XML RAW, XML AUTO or XML PATH. There was nothing on XML EXPLICIT, XQuery, OPENXML.

4 questions were on the windowing functions. The list of answers were the same for each question. The answers were RANK, NTILE, DENSE_RANK. Nothing on FIRST_VALUE, LAST_VALUE, LAG, LEAD. Nothing to do with offsets (ROWS BETWEEN UNBOUNDED PRECEDDING AND CURRENT ROW)

There were a few questions where there were code block and you had to drag and drop them in order. 1 was creating a union all between 2 tables, one was creating a table valued function that used a recursive CTE and the other was creating a indexed view.

No questions on any of the new date functions. A lot of questions on performance (which query is the best to use, i.e. which one makes the best use of SARGS).

1 or 2 questions that used TRY….CATCH.

A few questions on updating views through an instead of trigger.

1 question on using the UPDATE() trigger function.

How to join to a table valued function (APPLY)

Questions which showed you the DDL and asked you to create a process to do something (so create a stored proc, or a function).

1 question on ROLLUP vs GROUPING_SETS
0
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 25 total points
Comment Utility
@Dave

You can't ORDER BY a column that you didn't SELECT.

The column names referenced in the ORDER BY clause must correspond to either a column in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
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.

That looks correct to me.
"A relation (a Standard SQL term) has a body with a distinct set of tuples." = True
"A table (a T-SQL term) doesn’t have to have a key." = True

What part "isn't correct"?
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
Comment Utility
You're right Scott. I misread that. I've missed the bold part: "A table (a T-SQL term) doesn’t have to have a key. "
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now