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 appreciated and TIA

Greg

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

> 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.

Sorry, my typo.

Your second SELECT statement does do the trick.

Thanks.