On 2/4/16, Simon Slavin <slavins at bigfraud.org> wrote:
>
> 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
>
> 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.
>
SELECT *,
(SELECT max(booking_time) FROM bookings
WHERE bookings.roomid=room.roomid)
FROM room
WHERE ....;
I think the index you want is: "CREATE INDEX ex1 ON
bookings(roomid,booking_time);"
--
D. Richard Hipp
drh at sqlite.org
