select extra rows - 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)

select extra rows

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2010-10-27 : 17:27:06

testid name score section
1 Mark 100 A
2 Tom 90 B
3 Harry 80 C
4 Mark 70 D
1 Mark 100 A
2 Tom 90 B
3 Harry 80 C
4 Mark 70 D
1 Mark 100 A
3 Harry 80 C
1 Mark 100 A
3 Harry 80 C

Select name, score, section , count(1) cnt from dbo.test
group by name, score, section
having count(1) >1

name score section cnt
Harry 80 C 4
Mark 70 D 2
Mark 100 A 4
Tom 90 B 2

How can i get the extra rows.

desired output
1 Mark 100 A
2 Tom 90 B
3 Harry 80 C
4 Mark 70 D
1 Mark 100 A
3 Harry 80 C
1 Mark 100 A
3 Harry 80 C

8 rows. ( 4 original rows not selected)

Chandragupta Mourya

Takhyashila

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-27 : 18:04:25

please provide following


create table chandra(testid int, name nvarchar(50), score int, section nvarchar(25))

insert into chandra

If you don't have the passion to help people, you have no passion

notmyrealname

98 Posts

Posted - 2010-10-27 : 19:54:34

A little trick Madhivanan taught me...


SELECT     T1.testid, T1.name, T1.score, T1.section
FROM         (SELECT     COUNT(testid) - 1 AS Count, testid, name, score, section
                       FROM          test
                       GROUP BY testid, name, score, section) AS T1 INNER JOIN
                      master.dbo.spt_values AS T2 ON T1.Count > T2.number
WHERE     (t2.name IS NULL)

Using the master.dbo.spt_values you are sure to get a complete number list that starts with 0.

If on the other hand, your testid will always increment by 1, then you could just as well join your test table to itself like...


SELECT     T1.testid, T1.name, T1.score, T1.section
FROM         (SELECT     COUNT(testid) AS Count, testid, name, score, section
                       FROM          test
                       GROUP BY testid, name, score, section) AS T1 INNER JOIN
                          (SELECT     testid
                            FROM          test AS test_1
                            GROUP BY testid) AS T2 ON T1.Count > T2.testid

This returns the matches in the exact order you requeted.

Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources