help with ms query analyzer - running two or more commands..

Factual Questions
1

I want to do the following…
create view q1 as (select statement)
select (fields) from q1 where (conditions)

MS SQL analyzer won’t let me do this, it complains about the second select statement being incorrect “Incorrect syntax near the keyword ‘select’.”

It’s an annoyingly simple thing I’m trying to do!!
I am basically trying to do two things…

  1. save time and server load by first generating a table with a simple query (such as select all records for a specific date and account number)

  2. then find the first value and the last value of a certain column…which I can’t do in one go (unless there is some kind of ‘bottom 1’ command in sql???)
    for example…

create view q1

select top 1 balance from (select * from bigtable where ac = ‘1234’ and date = ‘20/10/2007’) order by time,
select top 1 balance from (select * from bigtable where ac = ‘1234’ and date = ‘20/10/2007’) order by time desc

as you can see both get the first (top 1 when sorted by earliest time) and last (top one when sorted by latest time) from the subquery (get all records for specific date and account number)

any easier way to construct the query without having to grab the ‘all records for specific date and account number’ query twice???

2

I think I solved it by adding a ‘go’ after the create view…
(If I run it twice I have to ‘drop view’ otherwise it complains there is already a view of that name