At 08:05 AM 5/02/2004 +0000, you wrote:

> > >select
> > >b1.book_id,
> > >CAST(0 AS DOUBLE PRECISION) as stock_id,
> > >b1.price,
> > >string2blob('') as condition from Book b1
> > >UNION
> > >select
> > >bk.book_id,
> > >sk.stock_id,
> > >sk.price,
> > >sk.condition
> > >from Book bk
> > >join Stock sk on bk.book_id = sk.book_id
>
>
> > Do you understand the difference between grouping and ordering in
>SQL? If
> > what you really want is ordering, then place the ORDER BY clause
>absolutely
> > last, after all of the SELECT specifications, and use the column
>numbers as
> > arguments.
>
>
>Ummm, well I think the idea of grouping by book_id was so that the
>stock id's (in the second select statement of the UNION) relating to a
>book would appear next to the book_id (of the first select statement
>of the UNION).

Grouping wouldn't do that. It is used for summarisation of multiple rows
into hiercharical groups and has nothing to do with adding columns to the
output set.

You just need to formulate the unions so that you get that correlated data
into the rowset of the union - possibly sthg like this:

select
b1.book_id,
CAST(0 AS DOUBLE PRECISION) as stock_id,
(select sk1.stock_id from stock sk1
where sk1.stock_id = b1.book_id) as new_stock_id,
b1.price,
string2blob('') as condition from Book b1
UNION
select
bk.book_id,
sk.stock_id,
sk.stock_id as new_stock_id,
sk.price,
sk.condition
from Book bk
join Stock sk on bk.book_id = sk.book_id

Then order the output any way you like:
ORDER BY 3, 4 DESC
would list all of the matching stock_id rows together, in price order, with
the most expensive first. Is this what you were thinking of as "grouping"?

>Perhaps creating a view is one way

That is SQL.

>or maybe sorting the array (using
>the Java Collections sort routine) or some such trick ....

That is not SQL. Presumably it is some kind of client-side operation -
Java fetching rows and buffering them in variant arrays? You don't need to
respond to that rhetorical question - it's off-topic.

/heLen