Solved

Does standard SQL have Order by clause?

Posted on 2015-02-01
19
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +7
19 Comments
 
LVL 37

Expert Comment

by:bbao
ID: 40583343
i think it is YES.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 40583357
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 11

Expert Comment

by:HuaMinChen
ID: 40583366
Yes, "Order by" is available to Sql and you should know which column you want to put the order.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40583386
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 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 25 total points
ID: 40583395
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 83

Expert Comment

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

Author Comment

by:Mike Eghtebas
ID: 40583403
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 83

Expert Comment

by:Dave Baldwin
ID: 40583446
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 75

Expert Comment

by:käµfm³d 👽
ID: 40583454
@Dave

You realize your comment contradicts your quote, right?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40583482
@kaufmed, I don't know what you're talking about.  ??
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40583505
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 35

Expert Comment

by:johnsone
ID: 40583747
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 66

Expert Comment

by:Jim Horn
ID: 40583902
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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40583912
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 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 25 total points
ID: 40584155
@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 50

Expert Comment

by:Vitor Montalvão
ID: 40584161
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:Scott Pletcher
Scott Pletcher earned 50 total points
ID: 40584281
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 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
ID: 40584289
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 34

Author Comment

by:Mike Eghtebas
ID: 40584369
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

729 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