Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    9

    Unanswered: 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
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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 .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •