Selecting the Record with Earliest Date in a Set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting the Record with Earliest Date in a Set

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-07-28 : 14:14:55

Hi,

I have two tables!

Hotel and HotelReservations

Here is the schema:

Hotel:
1) HotelId

HotelReservations:
1) HoterlReservationId
2) HotelId
3) Make
4) Model
5) DateEntered

Now, I want to join the two tables in such a way that I only get the row with the earliest date in the set between the Hotel and HotelReservation using HotelId.

This means that when I join Hotel and HotelReservations instead of returning all the matched HotelReservations records I want to only return the record which has the earliest day. Earliest = 07/28/2009 as opposed to 07/29/2009.

Thanks

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-07-28 : 14:19:32

Here is my query:

select ir.hotelid,ir.dateentered,ir.make, ir.model, ir.serialnum, count(cast(hotelid as varchar(30)))
from hotelreservations ir
where ir.hotelid= ( select top 1 hotel from hotel where hotelid= ir.hotelid order by dateentered asc)
group by hotelid, dateentered,make,model,serialnum

and i think it works and produces the correct result!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources