distinct with first record if find certain field m

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)

distinct with first record if find certain field m

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-08-26 : 07:41:38

I have
Code Company CompanyCount ContactPerson

702 Bistro Delifrance 1 NULL

97 Delifrance 15 NULL

98 Delifrance 15 NULL

99 Delifrance 16 NULL

99 Delifrance 15 NULL

100 Delifrance 15 NULL

100 Delifrance 15 test

I need the following result

Code Company CompanyCount ContactPerson

702 Bistro Delifrance 1 NULL

97 Delifrance 15 NULL

That is if company delifrance is repeated then just get its first record

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

ranganath
Posting Yak Master

209 Posts

Posted - 2008-08-26 : 07:51:05

hi,

try with this

Declare @Temp Table (Code Int, Company varchar(100), CompanyCount Int, ContactPerson varchar(100))
Insert into @Temp
Select 702, 'Bistro Delifrance', 1, NULL Union All
Select 97, 'Delifrance' ,15, NULL Union All
Select 98, 'Delifrance', 15, NULL Union All
Select 99, 'Delifrance' ,16, NULL Union All
Select 99, 'Delifrance' ,15, NULL Union All
Select 100, 'Delifrance' ,15, NULL Union All
Select 100, 'Delifrance', 15, 'test'

Select Code, Company, Companycount, ContactPerson
From (Select Code, Company, Companycount, ContactPerson, ROW_NUMBER() OVER (PARTITION BY Company order by CompanyCount)AS 'Row'
From @Temp )A
where Row = 1

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 07:52:48

Try this
Select * FROM
(
SELECT CODE,COMPPANY,COMPANYCOUNT ,CONTACTPERSON, ROW_NUMBER() OVER(PARTITION BY COMPANY,Delifrance
ORDER BY COMPANY )AS 'RID' FROM yourtable ) tbl WHERE RID=1

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-08-26 : 08:15:48

Thanks sunil.It worked

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-26 : 08:22:48

You are welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 08:27:58

quote:
Originally posted by sunil

Try this
Select * FROM
(
SELECT CODE,COMPPANY,COMPANYCOUNT ,CONTACTPERSON, ROW_NUMBER() OVER(PARTITION BY COMPANY~~,Delifrance~~
ORDER BY COMPANY )AS 'RID' FROM yourtable ) tbl WHERE RID=1

there's no field called delifrance in op's sample. thats just a value existing in company field

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 08:33:17

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources