moving unique casenum 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 2008 Forums

Transact-SQL (2008)

moving unique casenum rows

Author

Topic

DKV
Starting Member

5 Posts

Posted - 2010-08-04 : 14:41:16

I have data like this
Table A:
RecID CaseNum AID Cat1
1 case1 111 Normal
2 case2 222 Abnormal
3 case3 333 Failure
4 case2 222 Normal
5 case4 444 Abnormal
6 case5 555 Failure
7 case6 666 Normal
8 case4 444 Abnormal
9 case3 333 Failure

I want to move rows with with unique CaseNum in table B, irrespective of what is in AID and Cat1 columns, so that table B looks like this:
Table B:
RecID CaseNum AID Cat1
1 case1 111 Normal
2 case2 222 Abnormal
3 case3 333 Failure
5 case4 444 Abnormal
6 case5 555 Failure
7 case6 666 Normal

What is the best way to achieve that?

Thanks for your help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-04 : 15:53:43

[code]SELECT RecID,CaseNum,AID,Cat1 FROM
(
SELECT row_number() over(partition by CaseNum order by RecID) as seq, * from TableA
) t
WHERE t.seq = 1[/code]

mfemenel
Professor Frink

1421 Posts

Posted - 2010-08-04 : 15:55:03

SELECT MIN(recid),casenum,aid,cat1
FROM tablename
GROUP BY casenum,aid,cat1

Mike
"oh, that monkey is going to pay"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-04 : 15:59:25

quote:
Originally posted by mfemenel

SELECT MIN(recid),casenum,aid,cat1
FROM tablename
GROUP BY casenum,aid,cat1

Mike
"oh, that monkey is going to pay"

Mike..I don't think this will work for that sample data as the values for Cat1 are different for the same CaseNum.

mfemenel
Professor Frink

1421 Posts

Posted - 2010-08-04 : 16:10:52

ah, dang. Missed that one with the difft value.

Mike
"oh, that monkey is going to pay"

mfemenel
Professor Frink

1421 Posts

Posted - 2010-08-04 : 16:27:52

ok. we'll try that again

SELECT base.RecID, base.casenum, base.aid,cat1
FROM #temp t
INNER JOIN (
SELECT MIN(t.RecID)RecID
,CaseNum
,AID
FROM #temp t
GROUP BY CaseNum
,AID
)base
on t.recid=base.recid

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources