SQL Non Unique ID on Left joined with Non Unique ID on Right. Need 1 to 1 join.

1 01/01/2000
1 02/01/2000
1 03/01/2000

1 01/01/2001
1 02/01/2001
1 03/01/2001
1 04/01/2001
1 05/01/2001
1 06/01/2001

What I would like to do is take the values of Table1 and match the non unique id (in this case it's '1') with the earliest date to the earliest dates in Table2. It should be a 1 to 1 relationship.

Example of desired output: (Table1 values = Table2 Values)
1 01/01/2000 = 1 01/01/2001
1 02/01/2000 = 1 02/01/2001
1 03/01/2000 = 1 03/01/2001

So I need the cardinality of Table1(3 items) to match the output and ignore the other values.
I'd like to do this without pointers.
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.

Jim HornSQL Server Data DudeCommented:
Not getting how 2000 = 2001, but along the lines of 'earliest dates', copy-paste the below into your SSMS, execute to verify it meets your requirements, then modify to work with your object names
CREATE TABLE t1 (id int, dt date)

INSERT INTO t1 (id, dt) 
 (1, '01/01/2000'), (1, '02/01/2000'), (1, '03/01/2000')

CREATE TABLE t2 (id int, dt date)

INSERT INTO t2 (id, dt) 
 (1, '01/01/2000'), (1, '02/01/2000'), (1, '03/01/2000'),
 (1, '04/01/2000'), (1, '05/01/2000'), (1, '06/01/2000')

SELECT table_1.id, table_1.dt
	SELECT id, dt, RANK() OVER (PARTITION BY id ORDER BY dt) rank_order
	FROM t1) table_1
	SELECT id, dt, RANK() OVER (PARTITION BY id ORDER BY dt) rank_order
	FROM t2) table_2 ON table_1.id = table_2.id AND table_1.rank_order = table_2.rank_order
ORDER BY table_1.id, table_1.rank_order

Open in new window

Brian CroweDatabase AdministratorCommented:
WITH cteTable1 AS
      SELECT id, date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) AS RowNumber
      FROM Table1
cteTable2 AS
      SELECT id, date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) AS RowNumber
      FROM Table2
SELECT cteTable1.*, cteTable2.*
FROM cteTable1
INNER JOIN cteTable2
      ON cteTable1.id = cteTable2.id
      AND cteTable1.RowNumber = cteTable2.RowNumber
WHERE cteTable1.RowNumber = 1

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
ButtonDownBobby, do you still need help with this question?
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

From novice to tech pro — start learning today.