CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Graham
Graham

Reputation: 8151

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

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

Upvotes: 37

Views: 58357

Answers (2)

user10508443
user10508443

Reputation: 1

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

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

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

Upvotes: 57

Related Questions