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.
