2

This question got me thinking: ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C) In it the questioner states two functions give the same result set, however they don't in that they have identical rows but they are always in different (and consistent) orders.

Is the order of a result set part of the result set? Wikipedia implies no (but doesn't strongly determine an answer, nor does it speak from a position of authority):

https://en.wikipedia.org/wiki/Result_set

Is 

Sector  Name
A   Alpha
B   Beta

the same result set as

Sector  Name
B   Beta
A   Alpha

I personally only work with SQL Server, does the answer change by platform? Is the answer a matter of opinion only? (If so please give answers of fact only, or of consensus by professional bodies at any rate- not your personal opinion please)

Improve this question
0

3 Answers 3

Reset to default
4

Possibly the ANSI 92 definition held at

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

gives the answer. Section 3.1 Definitions seems to draw a distinction between the idea of a result set and a result sequence. The distinction was not in my awareness when I wrote the question using the former phrase

v) sequence: An ordered collection of objects that are not necessarily distinct.

w) set: An unordered collection of distinct objects. The collection may be empty.

Improve this answer
0
1

Ansi sql doesn't consider ordering a part of the result set - thus the 2 are identical. The result set is indeed a set.

Btw, the 'big players' (oracle,Microsoft...) follow the ansi sql quite closely, if they consider them the same so does the standard... (On most things)

Improve this answer
-1

The term "result set" [sic] does not appear in the 1992 SQL standard. It's typically used to mean the result of a select statement. Which is a partially ordered sequence of rows from a table:

20.2
Format
< direct select statement: multiple rows >
General Rules
5) If an < order by clause > is specified, then the ordering of rows of the result is effectively determined by the < order by clause > as follows:

From the 2003 SQL standard:

3.1.6.30 result set: A sequence of rows specified by a < cursor specification >, brought into existence by opening a cursor and ranged over by that cursor, and having operational properties of sensitivity, scrollability, holdability, and returnability.

3.1.6.32 returned result set: A result set created during execution of an SQL-invoked procedure and not destroyed when that execution terminates. Such a result set can be accessed by using a cursor other than the one that brought it into existence (a received cursor).

14.3
Format
< cursor specification > ::= < query expression > [ < order by clause > ] [ < updatability clause > ]

22.2
Function
Specify a statement to retrieve multiple rows from a specified table.
Format
< direct select statement: multiple rows > ::= < cursor specification >
Syntax Rules
2) The < cursor specification > shall not contain an < updatability clause >.
General Rules
1) Let Q be the result of the < cursor specification >.
2) Case:
a) If Q is empty, then a completion condition is raised: no data.
b) Otherwise, Q is not empty and Q is returned. The method of returning Q is implementation-defined.

(Clearly the result of a SELECT statement is ordered because that is what its ORDER BY does.)

Tables are unordered. But a result set [sic] is not a table.

Improve this answer
4
  • Your last sentence undoes you. The standard defines a set as being ordered. Thus you would have to refer to a result collection.
    – Paul
    Nov 16, 2018 at 8:21
  • 1
    @Paul Read what it says: "result set: A sequence". French toast is not toast. I would agree that the standard has a lot of poor wording. As I say arguments are moot because rows have to be returned per any ORDER BY.
    – philipxy
    Nov 16, 2018 at 9:40
  • 1
    I see my "2003" should be 2006. Quotes are from a draft version downloaded zipped from the Wikipedia SQL page. I see there's a link with text "SQL:2011 draft" there now. Quotes are from file 5CD2-02-Foundation-2006-01.pdf. Title page says ISO/IEC JTC 1/SC 32, Date: 2006-02-01, CD 9075-2:200x(E), ISO/IEC JTC 1/SC 32/WG 3". I had to add spaces after < & before > to defeat markup. PS Again, I don't know how you think a select statement does not return a sequence, although maybe you just don't think "result set" refers to that. The term predates SQL DBMSs & "set" wasn't meant mathematically.
    – philipxy
    Nov 16, 2018 at 11:11
  • Have you ever worked on a server that had a tempDB data file split into multiple files? And then run simple un-ordered queries on tables on that server? (e.g. select * from tblName) The results will come in different orders each time. Would you say the result is same each time, or different?
    – Paul
    Nov 16, 2018 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.