sql server :distinct 与order by 一起使用要注意

一次在sql server 2008 中写sql语句：select distinct firstname,lastname from person order by person_id

错误提示：

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

 

哦，原来在与distinct 一起时，order by 中出现的字段必须也在select 中出现啊！

改写如下：

select distinct firstname,lastname,person_id from person order by person_id

 

如果要简单，也可以这么写：

select distinct * from person order by person_id，不过这时候用distinct 就没有什么意义了。