Help needed with TSQL statement

Hi all,

I have a booking table that records changes to bookings to give an audit
trail.

Table1

ID - is an Identity column.
BookingID - is a varchar column containing the booking id of each booking.
Comment - is a varchar column containing details of the booking changes.
dtTime - a datetime column containing the date and time of the booking
change.

eg:

ID BookingID Comment dtTime
1 30 Initial booking
2004-07-08 13:36:23.363
4 31 Initial booking
2004-07-08 13:37:23.363
5 30 First change
2004-07-08 13:38:23.363
9 31 First change
2004-07-08 13:39:23.363
11 30 Second change
2004-07-08 13:40:23.363

Note there has been 2 changes to the intial booking for bookingID 30, and
one change for bookingID 31.

What I want is an SQL SELECT statement that will give me all the ID's for
the initial booking. In this example 1 and 4 should be the only results
that the statement will return.

Any help appreciated and TIA

Greg

Re: Help needed with TSQL statement

Greg Hines (ghines@aussiemail.com.au_NO_SPAM) writes:
> What I want is an SQL SELECT statement that will give me all the ID's for
> the initial booking. In this example 1 and 4 should be the only results
> that the statement will return.

SELECT ID, BookingID FROM tbl WHERE Comment = 'Initial booking'?

SELECT MIN(ID), BookingID FROM tbl GROUP BY BookingID?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Help needed with TSQL statement

> SELECT ID, BookingID FROM tbl WHERE Comment = 'Initial booking'?
Cannot use this as Initial booking comment may not always be the same, just
that it was in my example.

> SELECT MIN(ID), BookingID FROM tbl GROUP BY BookingID?

Gives the error:-

Column 'tbl.BookingID is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Re: Help needed with TSQL statement

Sorry, my typo.

Your second SELECT statement does do the trick.

Thanks.