September 20, 2005 at 11:21 am
Hi there,
very quick query- in our event booking system we have a table which has amongst other fields, the id of the agent booking, the id of the booking and the time of booking.
If i do a select on
SELECT agentid, max(booksaletime)
this will give me a list of all the agents and the last time they booked something
however, how would i then attach the relevant booking id to that.. eg if i use
SELECT agentid, bookingid, max(booksaletime)
then it obviously returns all agents, all their bookigns and the last sale time of that booking which in effect just gives me all bookings!
i know it's simple- help!
many thanks,
alex
September 20, 2005 at 11:29 am
Select dtLastEntries.AgentID, dtLastEntries.BookSaleTime, Main.BookingID from dbo.YourTable Main inner join
(Select agentid, max(booksaletime) as booksaletime from dbo.YourTable group by agentid) dtLastEntries
on Main.AgentID = dtLastEntries.AgentID and Main.BookSaleTime = dtLastEntries.BookSaleTime
September 20, 2005 at 11:32 am
hi cheers for the reply,
that's what i thought of doing but i wasnt sure whether there was a neater way of doing it than self joining on a date field
appears to have worked a treat though thanks again!
September 20, 2005 at 11:37 am
It's not about being neat... It's about filtering first, then showing the data. I don't know any other set way of doing this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply