ROW NUMBER LINKING based on date

Question

ROW NUMBER LINKING based on date

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

If I'm correct, you effectively want to match the Maximum RowNum in Table A to the Maximum RowNum in Table B, and then work your way down, correct? Using a CTE, you could create a new Row Number to join on. Something like:
WITH TableA AS ( SELECT EMPNO, OCODE,STARTDATE, ENDDATE,ROWNUM, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS JOINID FROM [Table A]) , TableB AS ( SELECT EMPNO, CODE,STARTDATE,ROWNUM, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS JOINID FROM [Table B]) SELECT * FROM TableA A JOIN TableB B ON A.JOINID = B.JOINID;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Johnny D Ten Centuries Points: 1030 More actions · Answer 2

Hi Thom,

Thanks for your suggestion. Not sure if I've understood you correctly but that does not work as sometimes table B will have 6 rows and Table A will have 3 so rownum 6 will need to link with rown 3. Somehow the sql needs to say link the top 3 from Table A with the top 3 from Table B regardless of the row number. If that makes sense.

Thom A SSC Guru Points: 99143 More actions · Answer 3

The above would work. If [Table A] has 3 rows, and [Table B] has 6, then the links would go (A -> B):
1 -> 4
2 -> 5
3 -> 6

From what you've posted, that seems correct to me. If not, provide DDL and DLM, along with expected results.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Johnny D Ten Centuries Points: 1030 More actions · Answer 4

Hi Thom,
Excellent. After a second look it certainly does work. Just what I needed. Also only needed a slight change to my current script.
Thanks for your help.