#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    10
    Rep Power
    0

    Join of two tables on the basis of their row numbers


    Hello all,

    Can anyone tell me how to get Row Number of a tuple in the table?? I have seen window function but it can be used with orderby clause only & I dont want to sort tuples..Also I dont want to include a new index column I want to access tuples by their original Row Number.. Basically I want to perform join of two tables on the basis of their row numbers..
    Does anyone have any idea about this??

    Waiting for your reply,
    Regards,
    Aditi
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    There is no such thing as the "original row number" in a relational database.

    A row number can only be defined when you use an order by.

    So assuming you have a column to sort both tables you can do the following:

    Code:
    with first_ordered (
       select *, 
              row_number() over (order by some_column) as rn
       from first_table
    ),
    second_ordered (
       select * 
              row_number() over (order by some_column) as rn
       from second_table
    ) 
    select fo.*, so.*
    from first_ordered fo
      join second_ordered so on fo.rn = so.rn
    The CTE (with ...) is not really necessary, you can join on derived tables as well, if you prefer that syntax:

    Code:
    select fo.*, so.*
    from (select *, 
                  row_number() over (order by some_column) as rn
          from first_table
    ) fo
    join (select *,
                 row_number() over (order by some_column) as rn
           from second_table) so on fo.rn = so.rn
    But why you want to do that I cannot imagine...
    Maybe if you told us what you really want to do, we could find a better (= less unpredictable) solution .

IMN logo majestic logo threadwatch logo seochat tools logo