Group by and having..

  • 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

  • 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

  • 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!

  • 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