SQL Grouping sets of data

I have the table below with the desired results column I would like to see.
I have a list Trade Id in Ascending order along with a Amount column and Abs of that amt column.  I want to group the data with the trans_id column having a asc sequence and the abs MTM column have the same amt values.

Desired Result      trans_id_2      mtm_usd            Abs MTM USD
1            1012256520      105759            105759
1            1012256521      -105759            105759
1            1012256522      105759            105759
2            1012260887      179475            179475
3            1012260888      -33192            33192
4            1012261131      -399710            399710
5            1012261132      -135510            135510
6            1012379109      -5286028                           5286028
6            1012379110      5286028            5286028
7            1012844947      -73966            73966
8            1012844948      7075            7075
9            1012844950      -498640            498640
9            1012844951      498640            498640
10            1012876186      -201260            201260
10            1012876187      201260            201260
10            1012876188      -201260            201260
10            1012876189      -201260            201260
10            1012876190      201260            201260
10            1012876191      -201260            201260
11            106991444      -5831520                          5831520
11            106991445      5831520            5831520
11            106991446      5831520            5831520
11            106991447      -5831520                           5831520
12            106991458      -378339            378339
12            106991459      378339            378339
12            106991460      378339            378339
12            106991461      -378339            378339
orod27x1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
can you please restate your question? Please post both sample data (input) and expected result (output)
0
orod27x1Author Commented:
Sure.

Here is the sample data:

trans_id_2       mtm_usd            Abs MTM USD
1012256520      105759            105759
1012256521      -105759            105759
1012256522      105759            105759
1012260887      179475            179475
1012260888      -33192            33192
1012261131      -399710            399710
1012261132      -135510            135510
1012379109      -5286028                           5286028
1012379110      5286028            5286028
1012844947      -73966            73966
1012844948      7075            7075
1012844950      -498640            498640
1012844951      498640            498640
1012876186      -201260            201260
1012876187      201260            201260
1012876188      -201260            201260
1012876189      -201260            201260
1012876190      201260            201260
1012876191      -201260            201260
106991444      -5831520                           5831520
106991445      5831520            5831520
106991446      5831520            5831520
106991447      -5831520                           5831520
106991458      -378339            378339
106991459      378339            378339
106991460      378339            378339
106991461      -378339            378339


and here are my desire results, which is a added column showing the groupings i would like:


Desired Rslt  trans_id_2      mtm_usd      Abs MTM USD
1      1012256520      105759      105759
1      1012256521      -105759      105759
1      1012256522      105759      105759
2      1012260887      179475      179475
3      1012260888      -33192      33192
4      1012261131      -399710      399710
5      1012261132      -135510      135510
6      1012379109      -5286028      5286028
6      1012379110      5286028      5286028
7      1012844947      -73966      73966
8      1012844948      7075      7075
9      1012844950      -498640      498640
9      1012844951      498640      498640
10      1012876186      -201260      201260
10      1012876187      201260      201260
10      1012876188      -201260      201260
10      1012876189      -201260      201260
10      1012876190      201260      201260
10      1012876191      -201260      201260
11      106991444      -5831520      5831520
11      106991445      5831520      5831520
11      106991446      5831520      5831520
11      106991447      -5831520      5831520
12      106991458      -378339      378339
12      106991459      378339      378339
12      106991460      378339      378339
12      106991461      -378339      378339

0
ralmadaCommented:
what about this?

select 	trans_id_2, 
	mtm_usd, 
	[abs mtm usd], 
	row_number() over (partition by [abs mtm usd] order by trans_id_2) rn
from table
order by trans_id_2

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ralmadaCommented:
actually, that won't work. try this
 

select 	trans_id_2, 
	mtm_usd, 
	[abs mtm usd], 
	dense_rank() over (partition by [abs mtm usd] order by trans_id_2) rn
from table
order by trans_id_2

Open in new window

0
orod27x1Author Commented:
Thats almost working... Here is a sample of the results.  But were the grouping set is 1,2,3  I want that to be just one number 1 and the next set 2 and so forth.

trans_id_2      mtm_usd      abs_mtm      rn      I want this
1012256520      105759      105759      1      1
1012256521      -105759      105759      2      1
1012256522      105759      105759      3      1
1012256523      73375      73375      1      2
1012256524      -73375      73375      2      2
1012256525      73375      73375      3      2
1012256526      -239989      239989      1      3
1012256527      239989      239989      2      3
1012256528      -239989      239989      3      3
1012256899      -320806      320806      1      4
1012256900      320806      320806      2      4
1012256901      -320806      320806      3      4
1012256902      0      0      1      5
1012256903      0      0      2      5
1012256904      0      0      3      5
1012260887      179475      179475      1      6
1012260888      -33192      33192      1      7
1012261131      -399710      399710      1      8
1012261132      -135510      135510      1      9
1012261134      14841      14841      1      10
0
ralmadaCommented:
try this way
select  trans_id_2,  
        mtm_usd,  
        [abs mtm usd],  
        dense_rank() over (order by trans_id_2) rn 
from table 
order by trans_id_2

Open in new window

0
orod27x1Author Commented:
That last code just gave me a straight count in sequence of the trans_id 1....60k
0
SharathData EngineerCommented:
try this
SELECT   trans_id_2, 
         mtm_usd, 
         [abs mtm usd], 
         Dense_rank() 
           OVER(ORDER BY [abs mtm usd]) rn 
FROM     table 
ORDER BY [abs mtm usd]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
orod27x1Author Commented:
I think that might have done the trick... looking good so far.. just checking data in full.. over 60k trans_Ids
0
orod27x1Author Commented:
yes that did the trick... Thanks so much.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.