Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    10

    Unanswered: Union All & Group By

    I am embarrassed to post this, it seems so simple.

    I have read the archives and the solutions do not seem to work with my code. My programing does not recognize the suggested commands.

    I am using Access2K and FrontPage2K. Hey, don't laugh.

    Here is the code:

    SELECT Parking
    FROM Bus
    WHERE BusNumber=::BusNumber::
    UNION ALL
    SELECT Parking2
    From Parking1

    The second SELECT results need to ASC alphabetically.

    Of course, GROUP BY applies itself to the whole query, which will not work for me.

    I tried to apply the solutions I found in the archives but either FrontPage or Access will not recognize the syntax.

    Thanks for any help.

    Rosenhan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, it's the other way around

    each subselect can have its own GROUP BY, but the overall UNION query can have only one ORDER BY

    does that help your situation?


    rudy
    http://r937.com/

  3. #3
    Join Date
    Feb 2003
    Posts
    10
    I appologize for wasting everyone's time. In my haste I have made the wrong post. THe post should have been:

    UNION ALL ORDER BY

    The problem is in the ORDER, Not GROUP

    SELECT Parking
    FROM Bus
    WHERE Bus.BusNumber=::BusNumber::
    UNION ALL
    SELECT Parking2
    From Parking1

    In this query, ORDER BY can only be applied at the end, and will ORDER the entire query.

    I only want to ORDER the second SELECT.

    Thanks

    Rosenhan

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --

    SELECT Parking
    FROM Bus
    WHERE Bus.BusNumber=::BusNumber::
    UNION ALL
    SELECT *
    from ( select Parking2 From Parking1 order by parking2 )

    or perhaps something like this

    SELECT 1 as sortkey1
    , ' ' as sortkey2
    , Parking
    FROM Bus
    WHERE BusNumber=::BusNumber::
    UNION ALL
    SELECT 2 as sortkey1
    , Parking2 as sortkey2
    , Parking2
    From Parking1
    order by sortkey1, sortkey2


    rudy

  5. #5
    Join Date
    Feb 2003
    Posts
    10
    Rudy,

    Your second example works correctly. The first, I knew from experience, would not work.

    I tried previously to apply my own version of your number 2 example, but could not get the syntax accepted. Yours works fine.

    Thank you very much.

    Rosenhan

Posting Permissions

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