Need help to built SQL - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to built SQL

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-06 : 20:03:12

My table and rows as follows,
t1
c1 c2 c3
---------------------------------------------------
BCTAN2RM BCTAN2SC30RM 20081016
BCTAN2RM TBC2SPORESUN30RM 20081016
BCTANRM BCTANSC30RM 20081016
BCTANRM TBCSPORESUNSC30RM 20081016
DEFAULTRM DEFAULT2RM 20080101
DEFAULTRM DEFAULT30BP2RM 20090608
DEFAULTRM DEFAULT30BPRM 20090608
DEFAULTRM DEFAULT30BPSUNRM 20090608
INETRM INETSATRM 20090812
INETRM INETSUNDAYRM 20090812

How to Select Distinct and the resultset as follow,
c1 c2 c3
---------------------------------------------------
BCTAN2RM BCTAN2SC30RM 20081016
BCTANRM BCTANSC30RM 20081016
DEFAULTRM DEFAULT30BP2RM 20090608
INETRM INETSUNDAYRM 20090812

I dont care the value of c2 and c3 (randomly pickup is ok). Me only care c1 is not repeated.

singularity
Posting Yak Master

153 Posts

Posted - 2010-02-06 : 21:20:54

Here's one way:


select c1, c2, c3
from
(select c1, c2, c3, row_number() over (partition by c1 order by newid()) as rn
from t1) a
where rn = 1

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-07 : 00:37:41

tq very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources