How to do an inner join on row number in sql server

31
votes

SQL Server 2008

Two tables:

Table A has following data:

RowA
RowB
RowC
RowD

Table B has following data:

Row4
Row3
Row2
Row1

I want to get the following output:

RowA Row1
RowB Row2
RowC Row3
RowD Row4

The only common value between the two tables is the row number

I can get the data individually of course:

SELECT val
FROM A
ORDER BY val

SELECT val
FROM B
ORDER BY val

But how do I join on the row number?

And what if I don't have an order-by, but just want the rows in the order they come out?

RowA Row4
RowB Row3
RowC Row2
RowD Row1

as in the join of

SELECT val
FROM A

SELECT val
FROM B
2
sqlsql-serversql-server-2008
Mind you: as long as you don't explicitly specify an ORDER BY, the order in which the rows are returned from the table(s) is not guaranteed. It could be different tomorrow ... so linking two rows from two different tables based solely on their "ordinal" position in a more or less random output sequence is not reproducible and not reliable. If your two tables don't have anything in common - basically, you cannot really "connect" two datasets from either table... – marc_s
I realise that, but in my real life case, the two tables are from functions and will always be in the same order – Graham

2 Answers

47
votes

try this:

USE ROW_NUMBER() function in sql server 2008

select A.val,B.val 
from(
    SELECT val,row_number() over (order by val) as row_num
    FROM A)A
join
    (SELECT val,row_number() over (order by val) as row_num
    FROM B)B
on  A.row_num=B.row_num
ORDER BY A.val,B.val


SQL fiddle demo

0
votes
select A.sectionname, A.Basicprice,A.Brand,A.Description,A.Description,A.Grandtotal,A.GST,A.Gstvalue,A.Hsncode,A.Image,A.Installationcharge,A.Model,B.Age,B.Mark,B.Name,B.class

from(
   SELECT Class,row_number() over (order by class) as row_num,Age,Mark,Name
   FROM classtable)B
   LEFT join
   (SELECT sectionname,row_number() over (order by sectionname) as row_num,Basicprice,Brand,Description,Grandtotal,GST,Gstvalue,Hsncode,Image,Installationcharge,Model
   FROM testtable)A

on  A.row_num=B.row_num