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?
 
SharathConnect With a Mentor Data 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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.