Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-23 : 10:35:53
|
| chris writes "Is it true that using a Select Distinct always returns the values in order, ascending?So in the following SQL:Select distinct last_name from student order by last_namethe 'order by' phrase is redundant." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-23 : 10:44:35
|
| In this simple case, yes. If you look at the query plan, the distinct does a sort/distinct sort op. . .however, select distinct last_name, first_name from student it gets more complicated because its no longer a simple sort/distinct sort, rather it does a stream aggregate result in the resultset sort by one column or the other depending on indexing and/or selectivity. In this case the order by is required to ensure the resultset is ordered by last_name . . .<O> |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-05-23 : 10:50:48
|
No. The order of rows in results is only specificied if there is an ORDER BY in the outermost SELECT of the query. If the execution plan uses a hash aggregation then the rows will typically not be in a discernable order. Prior to version 7, the only GROUP BY technique used was to sort and discard duplicates, so rows would appear to be grouped in an order.<strange-looking snipy thing> Edited by - Arnold Fribble on 05/23/2002 10:52:02 |
|
|
|
|
|
|
is ORDER BY redundant if SELECT DISTINCT is used?