On Thu, Feb 4, 2016 at 12:32 PM, Simon Slavin <slavins at bigfraud.org> 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. > ?CTE to the rescue! WITH SELECT ID, MAX(DateTime_Column) as LastDateTime FROM bookings GROUP BY ID? AS room_last_used SELECT rooms.ID, rooms.othercolumns, room_last_used.LastDateTime FROM rooms JOIN room_last_used ON rooms.ID = room_last_used.ID ORDER BY ... ; Note - I'm old. I have arthritis. My typing sometimes stinks (like my feet). And I can't test the above because I ain't got no data. -- Werner Heisenberg is driving down the autobahn. A police officer pulls him over. The officer says, "Excuse me, sir, do you know how fast you were going?" "No," replies Dr. Heisenberg, "but I know where I am." Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. He's about as useful as a wax frying pan. Maranatha! <>< John McKown
