Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: union all and order by .........9i

    Hi guys

    Can I join multiple queries and have each query use
    Code:
    'order by COL1'
    ?

    Using 'ORDER BY COL1' in the end although works doesn't give me what I want.

    Actually, I tried but it's not working.

    Regards
    Shajju

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not sure if the syntax will work exactly as coded, but the idea is you add another column to all the SELECT clauses which is used as the main sort key, then sort the col1 values within that
    Code:
    SELECT 1 AS  sortkey
         , col1
      FROM table1
    UNION ALL
    SELECT 2 AS  sortkey
         , col1
      FROM table2
    UNION ALL
    SELECT 3 AS  sortkey
         , col1
      FROM table3
    ORDER
        BY sortkey
         , col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Not sure how to use the sort key logic to my query below so I don't have to use 'ORDER BY':

    Code:
    (SELECT DATETIME,BSC,CELL,CALL_EST_DAY_BEFORE_YESTERDAY,CALL_EST_YESTERDAY,CALL_EST_DIFF, CALL_EST_DIFF_PERCENT from
    (
    Select A.DATETIME DATETIME,A.BSC BSC,A.CELL CELL,B.CALL_EST CALL_EST_DAY_BEFORE_YESTERDAY,A.CALL_EST CALL_EST_YESTERDAY, (A.CALL_EST-B.CALL_EST) CALL_EST_DIFF, 
    
    ROUND(DIV((A.CALL_EST-B.CALL_EST),A.CALL_EST)*100,2) CALL_EST_DIFF_PERCENT
    
    from
    (SELECT to_char(DATETIME,'mm/dd/yyyy') datetime, BSC, CELL, CALL_EST
    FROM SCHEMA.TABLE 
    WHERE DATETIME BETWEEN trunc(SYSDATE)-1 and trunc(SYSDATE)-1/3600) A 
    ,
    (SELECT CELL,CALL_EST
    FROM SCHEMA.TABLE WHERE DATETIME BETWEEN trunc(SYSDATE)-2 and trunc(SYSDATE)-1-1/3600) B 
    
    WHERE A.CELL=B.CELL)
    where CALL_EST_DIFF_PERCENT <-49)
    order by CALL_EST_DIFF_PERCENT

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If/when returned rows need to be presented in a specific order, then ORDER BY is required.
    If rows can be presented in any (random) order, then ORDER BY is not included.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    easy,

    select col1,col2,col3
    from
    (select col1,col2,col3
    from table1
    union all
    select col1,col2,col3
    from table2)
    order by col1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Thanks but I was looking for:

    (select col1,col2,col3
    from table1
    (order by col1)
    union all
    select col1,col2,col3
    from table2
    (order by col1)

    you see I'm looking to group by col2 and order by col1.

    (If I don't order by col1, the result is automatically grouped by col2. If I order the union of both selects by col1, then the result is not grouped by col2)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shajju View Post
    you see I'm looking to group by col2 and order by col1.
    sorry, this does not make sense

    could you restate this using actual column names?

    i'm having trouble linking "col1" and "col2" et cetera to your actual query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There is NO grouping in your example query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by beilstwh View Post
    There is NO grouping in your example query.
    bill, i think he meant that he wants all the rows with the same col2 value to be together, and within those, to be sorted by col1

    in other words, he wants ORDER BY col2,col1

    i've seen many people use this meaning for "grouping" and they obviously don't mean the aggregation of multiple detail rows into a single grouped aggregate row as preformed by GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool And no aggregates either

    Quote Originally Posted by beilstwh View Post
    There is NO grouping in your example query.
    And there are no aggregate functions either!

    Also, please format your queries using the "code" tags:
    Code:
    SELECT   Datetime, Bsc, Cell, Call_est_day_before_yesterday,
              Call_est_yesterday, Call_est_diff, Call_est_diff_percent
       FROM   (SELECT   A.Datetime Datetime, A.Bsc Bsc, A.Cell Cell,
                        B.Call_est Call_est_day_before_yesterday,
                        A.Call_est Call_est_yesterday,
                        (A.Call_est - B.Call_est) Call_est_diff,
                        ROUND (Div ( (A.Call_est - B.Call_est), A.Call_est) * 100,2)
                            Call_est_diff_percent
                 FROM   (SELECT   TO_CHAR (Datetime, 'mm/dd/yyyy') Datetime, Bsc,
                                  Cell, Call_est
                           FROM   Schema.Table
                          WHERE   Datetime BETWEEN TRUNC (SYSDATE) - 1
                                               AND  TRUNC (SYSDATE) - 1 / 3600) A,
                        (SELECT   Cell, Call_est
                           FROM   Schema.Table
                          WHERE   Datetime BETWEEN TRUNC (SYSDATE) - 2
                                               AND    TRUNC (SYSDATE)
                                                    - 1
                                                    - 1 / 3600) B
                WHERE   A.Cell = B.Cell)
      WHERE   Call_est_diff_percent < -49
    ORDER BY   Call_est_diff_percent;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Nov 2003
    Posts
    76

    sub grouping the union

    try this and see if it works.

    select * from (
    select distinct col1, col2 from schema.table1
    where bla_bla = 'bla'
    order by 1)
    union
    select * from (
    select distinct col1, col2 from schema.table2
    where blu_blu = 'blu'
    order by 1);

Posting Permissions

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