ORDER BY Clause and Identical Values

[smacream]

Hi,

I have quick question regarding behaviour of ORDER BY clause in SELECT statement. If I choose to order by a particular field, and there are several rows with same value for that field how are they ordered? Do they appear as in their relative order for the result set without the order by clause?

e.g. suppose "SELECT * FROM my_table" returns:
| col1 | col2 | col3 |
| a | A | 1 |
| b | B | 2 |
| c | C | 2 |
| d | D | 2 |
| e | E | 0 |

If I use the query "SELECT * FROM my_table ORDER BY col3" am I always certain to get:
| col1 | col2 | col3 |
| e | E | 0 |
| a | A | 1 |
| b | B | 2 |
| c | C | 2 |
| d | D | 2 |

and not say:
| col1 | col2 | col3 |
| e | E | 0 |
| a | A | 1 |
| b | B | 2 |
| d | D | 2 |
| c | C | 2 |

I've run simple tests and that seems to be the case, but I've yet to find any documentation that states this explicitly, so answers welcome!

Thanks,

Seosamh

 

[r937]

Do they appear as in their relative order for the result set without the order by clause?

no, it's arbitrary


if you want to sequence the rows which have the same value for the sort column, just add another sort column

... ORDER BY col3, col2

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[smacream]

Ok. Thanks r937.

We're dealing with data provided by a third party that has a time stamp to millisecond precision. We normally require the data returned in some chronological form (there aren't really any other fields it would make sense to sort by). However, sometimes consecutive points in the data have millisecond precision. I'm pretty sure that the order they appear in the input datafiles (that are used to create the tables) is the right (chronological) order. I'm just not certain whether this order is maintained when the tables are queried. However, I guess the order is not certain to be maintained anyway, with or without "ORDER BY" clause anyway.

Thanks again.