[Rosenhan]

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

[r937]

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/

[Rosenhan]

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

[r937]

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

[Rosenhan]

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