[sqlite] Efficient relational SELECT

[Simon Slavin]

All suggestions (with a bug-fix or two) gave the same results and ran in 
acceptable time.  Thanks to everyone for their help.  I went with the 
sub-SELECT solution.  Not because it gave superior results but because I'm not 
very familiar with sub-SELECT and it's good to have a working example in my 
code I can copy when I need it elsewhere.

Once again this list came thing.  Thanks.

Simon.

[sqlite] Efficient relational SELECT

[Jake Thaw]

Hi Simon,

I do this type of query all the time to avoid sub queries and aggregation.
This might be what you are looking for to satisfy the elegance criteria:

SELECT r.room_id,
   b.date
  FROM roomr
  LEFT JOIN
   booking b  ON r.room_id = b.room_id
  LEFT JOIN
   booking b2 ON r.room_id = b2.room_id
 AND b2.date > b.date
 WHERE b2.booking_id IS NULL

I leave it to you to understand why this works.

Regards

Jake

[sqlite] Efficient relational SELECT

[Yannick Duchêne]

On Thu, 4 Feb 2016 20:40:56 +
Simon Slavin  wrote:

> 
> On 4 Feb 2016, at 7:16pm, Luuk  wrote:
> 
> > 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
> 
> That's the solution (a phrasing of it) that I thought of.  However, in my 
> head it's inefficient because of the test for MAX.  I was hoping for 
> something that looked better.  But it is plenty fast enough for my purposes 
> so any objection is purely for elegance and not for logic.
> 
> Thanks to you, John McKown and Dr Hipp.  I'll have a field day testing these 
> out tomorrow.
> 
> Simon.

So what about a trigger storing the last booking date/time in a dedicated table?

-- 
Yannick Duch?ne

[sqlite] Efficient relational SELECT

[Simon Slavin]

On 4 Feb 2016, at 7:16pm, Luuk  wrote:

> 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

That's the solution (a phrasing of it) that I thought of.  However, in my head 
it's inefficient because of the test for MAX.  I was hoping for something that 
looked better.  But it is plenty fast enough for my purposes so any objection 
is purely for elegance and not for logic.

Thanks to you, John McKown and Dr Hipp.  I'll have a field day testing these 
out tomorrow.

Simon.

[sqlite] Efficient relational SELECT

[Luuk]

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


  This email has been sent from a
virus-free computer not protected by Avast.
www.avast.com  

[sqlite] Efficient relational SELECT

[Simon Slavin]

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] Efficient relational SELECT

[Richard Hipp]

On 2/4/16, Simon Slavin  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

[sqlite] Efficient relational SELECT

[Jim Callahan]

Might want to split  bookings into a transaction table and a reservation
table.

The reservation table would have one column for each room and one row for
each calendar day (assuming this is a respectable joint with no hourly
reservations!).

Reservation table has primary key of date and room entries start out as NA.
As rooms are booked NAs are replaced with IDs from booking-transaction
table.

Jim
 This email has been sent from a
virus-free computer protected by Avast.
www.avast.com 
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Thu, Feb 4, 2016 at 1:32 PM, 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
>

[sqlite] Efficient relational SELECT

[John McKown]

On Thu, Feb 4, 2016 at 12:32 PM, 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.
>

?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