Menu
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 
408
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition. The following shows the syntax of the ROW_NUMBER() function: ROW_NUMBER() OVER ( [PARTITION BY partition_expression, ... ]
Inner Join can for sure return more records than the records of the table. Inner join returns the results based on the condition specified in the JOIN condition. If there are more rows that satisfy the condition (as seen in query 2), it will return you more results.
The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows. OVER - Specify the order of the rows.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
