How to query without duplicates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to query without duplicates?

Author

Topic

anabelle
Starting Member

19 Posts

Posted - 2009-04-15 : 03:59:20

I got a scenario here wherein i have to query a records which wud not duplicates:i have the ff query statement
select (*)from tablesample
group by Pro1,pro2,name
having by (count(pro1)>1) it display the following
Pro1 Pro2 Name
1 T1 Emma
1 T1 Lourdes
1 T1 Rems
1 T1 Myra
1 T1 Elma
2 T2 Emmanuel
2 T2 Erns
3 T3 Hana
4 T4 Glen

what i would like to happen is to display only the ff:
Pro1 Pro2 Name
1 T1 Emma
2 T2 Emmanuel
3 T3 Hana
4 T4 Glen

How wud i renovate my sql statement so that it will only display and query single record with single name..?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 04:49:19

SELECT Pro1, Pro2, Name
FROM (
SELECT Pro1, Pro2, Name, ROW_NUMBER() OVER (PARTITION BY Pro1 ORDER BY Name) AS recID
FROM [TableSample]
) AS d
WHERE recID = 1

E 12°55'05.63"
N 56°04'39.26"

anabelle
Starting Member

19 Posts

Posted - 2009-04-15 : 05:38:27

Peso, i am having that syntax error that is something on ur row_number()over (OVER (PARTITION BY Pro1 ORDER BY Name) AS recID

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 05:41:58

Change your compatibility level for the current database to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 07:01:06

try like this too
declare @tbl as table(Pro1 INT, Pro2 VARCHAR(4),[Name] varchar(40))
insert into @tbl select
1, 'T1', 'Emma' union all select
1, 'T1', 'Lourdes' union all select
1, 'T1', 'Rems' union all select
1, 'T1', 'Myra' union all select
1, 'T1', 'Elma' union all select
2, 'T2', 'Emmanuel' union all select
2, 'T2', 'Erns' union all select
3, 'T3', 'Hana' union all select
4, 'T4', 'Glen'

select identity(int,1,1) as rid, * into #temp from @tbl

select pro1,pro2,[name] from (
select *,(select count(*) from #temp where rid <= t.rid and Pro2 = t.Pro2) as cnt
from #temp t )s where s.cnt = 1

drop table #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources