SQLite / SQL question

HoMa

Well - I hope you don't mind that this is kind of off topic, because the focus of this question - as the topic name suggests - is on sqlite or maybe sql. But as I am doing my project in Qt and so far I got nice and good advice here, I will give it a try:

Let's asume we have two tables like "customer" and "booking". they are related by the field "customerid" in booking, which has to be from "id" in customer. Now check out the following Query:

SELECT * 
FROM customer LEFT JOIN booking ON customer.id = booking.customerid
GROUP BY customer.id

As expected I get one entry per customer. If there is no booking, all values from that table are NULL.
If there are bookings, the values from the first booking are in the resulting record.

My question is now:
The fact that it is always the first booking in the result - is this "by accident" or is there any (sql) rule to inforce this? Would a result set that gives me any one of the bookings be correct, too?
Or to phrase it differently: Can I be sure, that I will always get the first booking?

Let me add a question: What if I would want the last of the bookings in the resulting records? How can I achive this?

JonB

@HoMa said in SQLite / SQL question:

Would a result set that gives me any one of the bookings be correct, too?

Would this be answered in https://sqlite.org/lang_select.html#resultset and fall under [my italics]:

Otherwise [non-aggregate expression], it is evaluated against a single arbitrarily chosen row from within the group.

HoMa

WOW - good spotting! I think in the document you posted the answer is twice (and I missed them both):

I missed the sentence you quoted, because I stopped at the "If a Having clause is specified" - my mistake.
In addition: In the following "Side note" paragraphe there is an example very similar to mine with explanation (I will paste it below, in case anyone is interested.

So: Thanks a lot! You nailed it.
This now leaves me with a new problem: In my actual problem, the "bookings" have a date, and I would like to have values that match the first and last booking within one query. Any idea how I can solve that? I will ponder this a bit, and create a separate request, if I don't find a solution.

[from the same page as sited above:]
Side note: Bare columns in an aggregate queries. The usual case is that all column names in an aggregate query are either arguments to aggregate functions or else appear in the GROUP BY clause. A result column which contains a column name that is not within an aggregate function and that does not appear in the GROUP BY clause (if one exists) is called a "bare" column. Example:

SELECT a, b, sum(c) FROM tab1 GROUP BY a;
In the query above, the "a" column is part of the GROUP BY clause and so each row of the output contains one of the distinct values for "a". The "c" column is contained within the sum() aggregate function and so that output column is the sum of all "c" values in rows that have the same value for "a". But what is the result of the bare column "b"? The answer is that the "b" result will be the value for "b" in one of the input rows that form the aggregate. The problem is that you usually do not know which input row is used to compute "b", and so in many cases the value for "b" is undefined.