查询数据的问题

[yuyangyangde]

有这么一张表，数据类似如下：
col1 col2 col3
a1 b1 c1
a1 b1 c1
a1 b2 c3
a2 b2 c3
a2 b2 c4
a2 b3 c5
a3 b4 c6


现在有如下要求：
以col1为主，查询一张表，表中只要求col1列中的数据不能重复，请问如何查询

[中国风]

用 group by Col1

[NBDBA]

not exists
+
group by

[中国风]

or Cte

select *
from 
(select Row=row_number()over(partition by Col1 order by Col1),* from T1)t
where Row=1

[NBDBA]

or

not exists
+
distinct

[NBDBA]

select distinct *
from tab a
where not exists (
select 1 from tab
where col1=a.col1 and ( col2 >col2
or col2 = a.col2 and col3>a.col3)
)

[yuyangyangde]

这张表所有列的数据我都要查询出来

[AcHerat]

select distinct *
from tb t
where not exists (select 1 from tb where col1=t.col1 and (col2>t.col2 or (col2=t.col2 and col3>t.col3)))

--or

select col1,col2,col3
from(
    select *,rid=row_number() over (partition by col1 order by getdate())
    from tb
)t
where rid = 1

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-08-11 21:16:57
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(2),[col2] varchar(2),[col3] varchar(2))
insert [tb]
select 'a1','b1','c1' union all
select 'a1','b1','c1' union all
select 'a1','b2','c3' union all
select 'a2','b2','c3' union all
select 'a2','b2','c4' union all
select 'a2','b3','c5' union all
select 'a3','b4','c6'
--------------开始查询--------------------------
select
 * 
 from
  tb t 
 where
  not exists(select 1 from tb where (col1=t.col1 and col2>t.col2) or (col1=t.col1  and col3>t.col3))
----------------结果----------------------------
/* col1 col2 col3
---- ---- ----
a1   b2   c3
a2   b3   c5
a3   b4   c6

(3 行受影响)
*/

[cxmcxm]

select * from 
(select distinct col1,col2,col3 from t) a
where not exists(select * from t where col1.a.col1 and not (col2>=a.col2 or col3>=a.col3))

[cxmcxm]

declare  @tb table([col1] varchar(2),[col2] varchar(2),[col3] varchar(2))
insert @tb
select 'a1','b1','c1' union all
select 'a1','b1','c1' union all
select 'a1','b2','c3' union all
select 'a2','b2','c3' union all
select 'a2','b2','c4' union all
select 'a2','b3','c5' union all
select 'a3','b4','c6'

select * from 
(select distinct col1,col2,col3 from @tb) a
where not exists(select * from @tb where col1=a.col1 and 
(col2>=a.col2 and col3>a.col3 or col2>a.col2 and col3>=a.col3))

[q465897859]

select col1,
      MAX(case when RIGHT(col1,1)<>RIGHT(col2,1) then col2 else '' end) as col2,
      MAX(case when  RIGHT(col2,1)<>RIGHT(col3,1)  then col3 else '' end) as col3
from tb        
group by col1

[gogodiy]

给出你想要的结果。