On 04-02-16 19:32, Simon Slavin wrote: > Simplified explanation. Here's the setup: > > Two tables: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > Date/time is encoded as a long COLLATEable string. In other words sorting a > column by my date/time format will sort correctly into date order. > > Bookings may be made in reverse order. In other words, someone may book a > room for next month, and after that someone may book the same room for > tomorrow. > > System contains about 50 rooms and 5000 bookings. I can create whatever > indexes you want. > > Requirement: > > I want to show an HTML table which lists some rooms (using WHERE and ORDER > BY) and the latest time each of those rooms is booked for. At the moment my > code uses one SELECT to get room details and one SELECT for each room to find > the most recent booking. > > The obvious thing is to combine the two using JOIN. But the best JOIN I can > think of has to find the most recent booking using a clunky test for the > biggest date. And I can't figure out a good phrasing for a sub-select. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Will this simple solution be too slow?: SELECT id, room, date, time FROM rooms LEFT JOIN bookngs ON rooms.id=bookings.room GROUP BY rooms.id, bookings.date HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL <https://www.avast.com/sig-email> This email has been sent from a virus-free computer not protected by Avast. www.avast.com <https://www.avast.com/sig-email>
