Select Query to get all rows with same combination

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 2008 Forums

Transact-SQL (2008)

Select Query to get all rows with same combination

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-24 : 06:14:01

I want to get all rows which are appearing more than once, with the following six field combination of
(yearnum, halfyearnum,monnum,qtryearnum,progid,projid) all of these are int fields.

in the below sample data rows 1 & 3 are same(2 rows same combination)

2, 6, and 7 are same (3 rows same combination)

Declare @Sample table (ID int, Actual float, budget float, earned float, remaining float, yearnum int, halfyearnum int, monnum int, qtryearnum int, progid int,projid int)

select 1, '122.23','0','0','0','2007','2','8','3','59','103' union all
select 2, '124.04','0','0','0','2007','1','6','2','59','103' union all
select 3, '0','0','132.09','0','2007','2','8','3','59','103' union all
select 4, '0','0','0','0','2007','2','3','1','59','103' union all
select 5, '0','0','0','0','2007','2','2','3','59','103' union all
select 6, '0','0','128.09','0','2007','1','6','2','59','103' union all
select 7, '0','0','0','0','2007','1','6','2','59','103'

thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 06:23:31

[code]
SELECT ID , Actual , budget , earned , remaining , yearnum , halfyearnum , monnum , qtryearnum , progid ,projid
FROM
(
SELECT COUNT(1) OVER (PARTITION BY yearnum, halfyearnum,monnum,qtryearnum,progid,projid) AS Occur,*
FROM table
)t
WHERE occur>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-08-24 : 11:31:50

Thank you Visakh,

is there a way to know the count also?

Declare @Sample table (ID int, Actual float, budget float, earned float, remaining float, yearnum int, halfyearnum int, monnum int, qtryearnum int, progid int,projid int)
insert into @Sample (ID,Actual, budget, earned, remaining, yearnum, halfyearnum, monnum, qtryearnum, progid,projid)
select 1, '122.23','0','0','0','2007','2','8','3','59','103' union all
select 2, '124.04','0','0','0','2007','1','6','2','59','103' union all
select 3, '0','0','132.09','0','2007','2','8','3','59','103' union all
select 4, '0','0','0','0','2007','2','3','1','59','103' union all
select 5, '0','0','0','0','2007','2','2','3','59','103' union all
select 6, '0','0','128.09','0','2007','1','6','2','59','103' union all
select 7, '0','0','0','0','2007','1','6','2','59','103'

SELECT ID , Actual , budget , earned , remaining , yearnum , halfyearnum , monnum , qtryearnum , progid ,projid
FROM
(
SELECT COUNT(1) OVER (PARTITION BY yearnum, halfyearnum,monnum,qtryearnum,progid,projid) AS Occur,*
FROM @Sample
)t
WHERE occur>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:13:12

quote:
Originally posted by cplusplus

Thank you Visakh,

is there a way to know the count also?

Declare @Sample table (ID int, Actual float, budget float, earned float, remaining float, yearnum int, halfyearnum int, monnum int, qtryearnum int, progid int,projid int)
insert into @Sample (ID,Actual, budget, earned, remaining, yearnum, halfyearnum, monnum, qtryearnum, progid,projid)
select 1, '122.23','0','0','0','2007','2','8','3','59','103' union all
select 2, '124.04','0','0','0','2007','1','6','2','59','103' union all
select 3, '0','0','132.09','0','2007','2','8','3','59','103' union all
select 4, '0','0','0','0','2007','2','3','1','59','103' union all
select 5, '0','0','0','0','2007','2','2','3','59','103' union all
select 6, '0','0','128.09','0','2007','1','6','2','59','103' union all
select 7, '0','0','0','0','2007','1','6','2','59','103'

SELECT ID , Actual , budget , earned , remaining , yearnum , halfyearnum , monnum , qtryearnum , progid ,projid ,Occur AS Cnt
FROM
(
SELECT COUNT(1) OVER (PARTITION BY yearnum, halfyearnum,monnum,qtryearnum,progid,projid) AS Occur,*
FROM @Sample
)t
WHERE occur>1

Make small modification above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources