[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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?
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 4
  • 3
  • 3
  • +7
6 Solutions
 
bbaoIT ConsultantCommented:
i think it is YES.
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
HuaMinChenBusiness AnalystCommented:
Yes, "Order by" is available to Sql and you should know which column you want to put the order.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
T-SQL, MS Access, MySQL, and PostgreSQL all allow you to use the names of the columns in an ORDER BY clause.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
käµfm³d 👽Commented:
@Dave

You realize your comment contradicts your quote, right?
0
 
Dave BaldwinFixer of ProblemsCommented:
@kaufmed, I don't know what you're talking about.  ??
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
käµfm³d 👽Commented:
@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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
  • +7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now