SQL查询排序问题，希望大神帮忙解决！

[柒小沫]

建表语句：
CREATE TABLE Test(
[Id] [nvarchar](50) PRIMARY KEY NOT NULL,
[isCuiDan] [int] NOT NULL,
[ComeDate] [date] NOT NULL,
[ArriveTime] [int] NOT NULL,
[HotelName] [nvarchar](50) NOT NULL
)

插数据：
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('129324',0,'2012-08-19',9,'A')
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('235984',0,'2012-07-09',23,'C')
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('305241',1,'2013-03-03',18,'B')
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('240566',0,'2012-06-18',5,'A')
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('102351',0,'2012-09-10',17,'D')
INSERT INTO Test([Id],[isCuiDan],[ComeDate],[ArriveTime],[HotelName]) VALUES
('123052',0,'2013-04-03',19,'B')

现查询语句:
select * from Test t
order by t.isCuiDan desc,t.ComeDate,t.ArriveTime

查询结果：
Id isCuiDan ComeDate ArriveTime HotelName
------- ---------- ----------- --------- ---------
305241 1 2013-03-03 18 B
240566 0 2012-06-18 5 A
235984 0 2012-07-09 23 C
129324 0 2012-08-19 9 A
102351 0 2012-09-10 17 D
123052 0 2013-04-03 19 B

提问：
如何在上面的查询语句的基础上把结果变成：
Id isCuiDan ComeDate ArriveTime HotelName
------- ---------- ----------- --------- ---------
305241 1 2013-03-03 18 B
123052 0 2013-04-03 19 B
240566 0 2012-06-18 5 A
129324 0 2012-08-19 9 A
235984 0 2012-07-09 23 C
102351 0 2012-09-10 17 D
就是在上面排序以后的结果更具酒店名称如果出现了第一次那么接下来都是同酒店的，显示完以后再是别的

[专注or全面]

select * from test order by hotelname,ComeDate

[AcHerat]

select *,
    (case when 
          exists (select 1 from Test 
                  where HotelName = t.HotelName and isCuiDan = 1) 
     then 1 else 0 end) ocol
from Test t
order by ocal desc,HotelName,isCuiDan desc,ComeDate,ArriveTime