SQL 7.0 T-SQL error with UNION

[bbosley]

What is wrong with the following T-SQL running in SQL 7.0 against the pubs database?:

Select top 1
4 AS JustANumber, au_id, au_lname, au_fname, state, contract
from authors
where state = 'CA'
order by contract ASC
UNION
Select top 1
5 as JustANumber, au_id, au_lname, au_fname, state, contract
from authors
where state = 'UT'
order by contract ASC

This gives me an error:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'UNION'.

If I remove the ORDER BY clause from the first SELECT it runs. But I only want one record returned from each SELECT.

Any ideas?

Bryan

 

[redlam]

taking out the first 'order by' clause should eliminate the error and give you the expected result. when i run the same query, i only get one record for 'CA' and one record for 'UT'. maybe i misunderstood tho... X-)

 

[1AndyPandy]

Silly question I know but... why are you tring to Order a single record ??????

 

[Cagliostro]

order by must be at the end of whole query. This clause inside each union segment is not supported. John Fill

ivfmd@mail.md

 

[bbosley]

I used the following:

SELECT *
FROM (Select top 1
4 AS JustANumber, au_id, au_lname, au_fname, state, contract
from authors
where state = 'CA'
order by contract ASC) AS TEMP1
UNION
SELECT *
FROM (Select top 1
5 as JustANumber, au_id, au_lname, au_fname, state, contract
from authors
where state = 'UT'
order by contract ASC) AS TEMP2

This gives me the desired result in my more complex query.

Thanks for the help.

bbosley