distinct、top和order by

-- =============================================         
-- Author:      余波(杭州)         
-- Create date: 2011/09/20         
-- Description: distinct 和 order by;top 和 order by        
-- =============================================    
-- 关于order by 排序，如果select列表中未指定distinct，order by 后面可以跟from 表中的任何字段
-- 即使未包含在select 列表中，如果指定了distinct，则order by后面只能跟select 列表中的字段
-- 具体效果如下

-- 创建表和插入测试数据
create table a1
(
	id int not null,
	name int
)

insert into a1
select 1,2
union
select 2,3
union
select 3,4

----测试语句
select  id from a1 order by name
--有效
/* 运算结果
id
1
2
3
*/

select  distinct id from a1 order by name
-- 出现如下错误
-- ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

-----order by在没有加top之前，生成的结果是游标，不能用于表表达式，例如下面的表达式将返回错误
-----其中表表达式包括视图、内联表值函数、子查询，派生表和CTE
;with cte1 as
(
	select  * from a1 order by name
)select * from cte1
/*
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
*/

----但是加上top之后，top和order by组合返回的关系结果，能用户表表达式
;with cte1 as
(
	select  top 1 * from a1 order by name
)select * from cte1
/*
id	name
1	2
*/