Nrthugu

So I'm sure this is probably a duplicate question, but I haven't been able to find the right post with an answer. Looking for a solution that works in T-SQL.

The problem I am trying to solve is relating two tables with loosely coupled data. For example:

CREATE TABLE things1 (

  id INT NOT NULL,

  parentgroup CHAR(1),

  datecreated DATETIME NOT NULL)

GO



INSERT INTO things1

SELECT 1, 'A', '2018-10-01 01:00:00.000'

UNION ALL

SELECT 2, 'A' '2018-10-01 01:00:02.000'

UNION ALL

SELECT 3, 'A', '2018-10-01 01:00:02.345'

UNION ALL

SELECT 4, 'B', '2018-10-01 01:00:01.000'

UNION ALL

SELECT 5, 'B', '2018-10-01 01:00:03.000'

GO



CREATE TABLE things2 (

  id INT NOT NULL,

  parentgroup CHAR(1),

  datecreated DATETIME NOT NULL)

GO



INSERT INTO things2

SELECT 1, 'A', '2018-10-01 01:04:00.000'

UNION ALL

SELECT 2, 'A' '2018-10-01 01:05:12.000'

UNION ALL

SELECT 3, 'A', '2018-10-01 01:05:02.345'

UNION ALL

SELECT 4, 'A', '2018-10-01 01:06:01.000'

UNION ALL

SELECT 5, 'A', '2018-10-01 01:07:03.000'

UNION ALL

SELECT 6, 'B', '2018-10-01 01:04:08.000'

GO

And what I am trying to populate is:

CREATE TABLE things1xthings2 (thing1id INT, thing2id INT)

GO

The rules for how rows from these two tables need to be coupled is by sequencing their datecreated values for a given parentgroup (thus the title of this post). With the example data above, the rule would play out like:

thing1id    thing2id

--------    --------

1           1        (matching lowest datecreated for group A)

2           3        (matching next lowest)

3           2        (matching next lowest)

4           6        (matching lowest datecreated for group B)

Important point: it is possible for there to be more rows for a given group in either table. The "extras" just wouldn't have a mate or a row in the cross table.

I don't know of a way to accomplish this in a single set-based JOIN operation, but if there is a way to do that, I would love to see it.

My approach was to add a column to each table:

ALTER TABLE things1 ADD sequence INT

GO



ALTER TABLE things2 ADD sequence INT 

GO

This column would essentially turn the datecreated value into an enumerated sequence that could then be matched in a set-based JOIN operation to populate the cross table:

INSERT INTO things1xthings2

SELECT t1.id, t2.id

FROM things1 t1 

JOIN things2 t2 ON t2.parentgroup = t1.parentgroup

  AND t2.sequence = t1.sequence

The problem is I also don't know how to build the values of that sequence column. I just know the data would look like this when I'm done:

SELECT * FROM things1

id parentgroup datecreated             sequence

-- ----------- -----------             --------

1  A           2018-10-01 01:00:00.000 1

2  A           2018-10-01 01:00:02.000 2

3  A           2018-10-01 01:00:02.345 3

4  B           2018-10-01 01:00:01.000 1

5  B           2018-10-01 01:00:03.000 2



SELECT * FROM things2

id parentgroup datecreated             sequence

-- ----------- -----------             --------

1  A           2018-10-01 01:04:00.000 1

2  A           2018-10-01 01:05:02.345 2

3  A           2018-10-01 01:05:12.000 3

4  A           2018-10-01 01:06:01.000 4

5  A           2018-10-01 01:07:03.000 5

6  B           2018-10-01 01:04:08.000 1

Thanks for any help!

(Edit: My 'f' and 'd' keys were sticking!)

Many thanks to user @Shnugo for pointing me in the right direction. Using ROW_NUMBER() with a PARTITION BY and an ORDER BY in the OVER clause, I was able to make the desired associations in a single set operation, like so:

SELECT a.id, b.id

FROM (

    SELECT id, parentgroup, datecreated, 

        ROW_NUMBER() OVER(

            PARTITION BY parentgroup 

            ORDER BY datecreated) AS seq

    FROM things1) a

JOIN (

    SELECT id, parentgroup, datecreated, 

        ROW_NUMBER() OVER(

            PARTITION BY parentgroup 

            ORDER BY datecreated) AS seq

    FROM things2) b ON b.parentgroup = a.parentgroup 

AND b.seq = a.seq

Which, when run against the sample data above, produces exactly the associations desired:

1<->1

2<->3

3<->2

4<->6

Just add a simple INSERT and the table is populated exactly as needed.