1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

sorting by same date type column in different tables

Discussion in 'SQL PL/SQL' started by member.forums@gmail.com, Jul 16, 2009.

  1. member.forums@gmail.com

    member.forums@gmail.com Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I have two different tables both with 10 columsn (8 varchar , 1 date , 1 number)
    but the names are different in both for columns.
    Now I have to get the rows of both the tables altogether sorted by the time in the date columns

    like
    table 1 has a row with timestamp as 1100 (say)
    and table 2 has timestamp as 1200

    then the data in the output should contain data from row of tabel 1 and then row of data form table 2

    Sapan
     
  2. sashraf

    sashraf Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Hyderbad. India
    Code (SQL):
    SELECT   col1 AS col1, col2 AS col2, col3 AS col3 ..., col10 AS datecol
        FROM tab1
    UNION
    SELECT   col1 AS col1, col2 AS col2, col3 AS col3 ..., col10 AS datecol
        FROM tab2
    ORDER BY datecol
    You can use Union all or Union depending upon your requirement to display unique rows and display the rows from both the tables.

    ~Ashraf
     
  3. member.forums@gmail.com

    member.forums@gmail.com Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Thanks a lot
    one last thing does the last order by work for the second query or for the result of the union
     
  4. Sanjay MD

    Sanjay MD Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Mysore
    Dear Sapan,

    The order by works on the entire result and not on the 2nd query set. Though you can also encolse the union set in parenthesis for better visibility & conventions, as in

    Code (SQL):
    (SELECT col1
       FROM test1
     UNION
     SELECT col1
       FROM test2)
    ORDER BY col1 DESC;
    ------
    Also be sure that both the queries get the same number of columns..
     
  5. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    but here remember one thing that that the no of column you are selecting in from the first table must be equal to the no of column you are selecting from the second table, if there is a column that you need to select from the first table and there is no column in the second table then you need to type a null column in query of the second table like

    Code (SQL):
    (SELECT col1,
                col2
       FROM test1
     UNION
     SELECT col1,
                NULL
       FROM test2)
    ORDER BY col1 DESC;
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And even the datatype should be the same .
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    UNION should work for you in this case. Read here for examples of various UNION queries and the results they produce:

    http://oratips-ddf.blogspot.com/2008/07/preserving-union.html