Sql Grouping Similar Values Together

I am having a problem to group data. I have used the group by clause all the while, but this time I want to group similar valued data together. The need is I have some jobs which h

Solution 1:

This is a little more complex of a problem then I feel like thinking all the way through right now, but I'll give you an idea to start with and maybe someone else can help you complete it...

Join the table to itself like so:

Select  A.JobID, A.Sequence, Count(*) 

from    TheTable A join
        TheTable B on A.JobID <> B.JobID and A.Sequence = B.Sequence

groupby A.JobID

I haven't tested that so there could be typos, but you get the idea hopefully. Notice you're joining where the job is not the same, but the sequence is.

Solution 2:

Just inferring from other answers... something that may help.

Here you have for every two jobids how simmilar are:

http://sqlfiddle.com/#!3/c28be/9

Createtable Data(Job nvarchar(10), seq int);

insertinto data
SELECT'A01' ,8UNIONALLSELECT'A01',6UNIONALLSELECT'A01',10UNIONALLSELECT'A02',5UNIONALLSELECT'A02',10UNIONALLSELECT'A02',4UNIONALLSELECT'A02',2UNIONALLSELECT'A03',8UNIONALLSELECT'A03',3UNIONALLSELECT'A03',6UNIONALLSELECT'A03',10UNIONALLSELECT'A04',5UNIONALLSELECT'A04',4UNIONALLSELECT'A04',2UNIONALLSELECT'A04',9UNIONALLSELECT'A04',10;


select 
  d1.job as j1, 
  d2.job as j2, 
  count(*) cnt 
from Data d1 innerjoin Data d2 on (d1.seq = d2.seq and d1.job < d2.job)
groupby d1.job, d2.job
;

Solution 3:

Building on Brandon Moores answer:

Data setup:

DECLARE@DataTABLE (JobId nvarchar(10), Sequence int)
INSERTINTO@Data(JobId, Sequence)
SELECT'A01',8UNIONALLSELECT'A01',6UNIONALLSELECT'A01',10UNIONALLSELECT'A02',5UNIONALLSELECT'A02',10UNIONALLSELECT'A02',4UNIONALLSELECT'A02',2UNIONALLSELECT'A03',8UNIONALLSELECT'A03',3UNIONALLSELECT'A03',6UNIONALLSELECT'A03',10UNIONALLSELECT'A04',5UNIONALLSELECT'A04',4UNIONALLSELECT'A04',2UNIONALLSELECT'A04',9UNIONALLSELECT'A04',10UNIONALLSELECT'A05',100

Find totals of all sequences each JobID has in common, order those by most to least, output all the data from each JobId depending on that order:

;WITH cte AS (
    SELECT  A.JobID, A.Sequence, Count(*) AS [SequencesInCommon]
    FROM@Data A 
    LEFTOUTERJOIN@Data B on A.JobID <> B.JobID and A.Sequence = B.Sequence
    GROUPBY A.JobID, A.Sequence
),    
cte2 AS (
    SELECT JobID, SUM(SequencesInCommon) AS Total 
    FROM cte
    GROUPBY JobID
)    
SELECT  d.JobId, d.Sequence
FROM    cte2 c
INNERJOIN@Data d on c.jobID = d.JobID
ORDERBY c.Total ASC, c.JobID ASC

Gives:

JobId      Sequence
---------- -----------
A05        100
A01        8
A01        6
A01        10
A03        8
A03        3
A03        6
A03        10
A02        5
A02        10
A02        4
A02        2
A04        5
A04        4
A04        2
A04        9
A04        10

(17row(s) affected)

Should do it :)