I have a table with the structure

DateDone
<some values>

I had thought if I write:

SELECT
 MAX(DateDone) as MaxDate,
 Value1,
 Value2
FROM Table
WHERE SomeCondition

Value1 and Value2 would come from the ROW corresponding to MAX(DateDone)

But this doesn't happen. In fact Value1 and Value2 can be taken from ANY row that matches SomeCondition

It is possible to write

SELECT
 Value1,
 Value2
FROM Table
WHERE DateDone = (SELECT MAX(DateDone) FROM Table)

Which is fine if you only want one row, but falls apart if you want the values for each of some other field (ie Value for every Customer's latest order, not just the latest order in the table)

I must be missing something here ...

Adam

I am very confused as to a) the results you are getting and b) what it is that you want.

>I had thought if I write:
>
>SELECT
> MAX(DateDone) as MaxDate,
> Value1,
> Value2
>FROM Table
>WHERE SomeCondition
>
>Value1 and Value2 would come from the ROW corresponding to MAX(DateDone)
>
>But this doesn't happen. In fact Value1 and Value2 can be taken from ANY row that matches SomeCondition

In my experience you should get ALL the rows that match SomeCondition, Value1 & Value2 will be the values for that row and MAX(DateDone) will be the maximum value of DateDone for the rows selected by SomeCondition

>
>It is possible to write
>
>SELECT
> Value1,
> Value2
>FROM Table
>WHERE DateDone = (SELECT MAX(DateDone) FROM Table)
>
>Which is fine if you only want one row, but falls apart if you want the values for each of some other field (ie Value for every Customer's latest order, not just the latest order in the table)
>
>I must be missing something here ...

An example is definitely missing, at least for my poor old tired brain

It may be that GROUP BY would give you what you want, but you have to be careful with it because whilst aggregate values will be correct simple row values rely on the physical order of the table. This is often no problem but also often give daft results.

I think I know what yiu're after and if you can email me, or post to the binaries a table with data (use EDBManager and drag table to blank sql page)  I'll have a look for you.

Roy

Dear Roy,

Thanks for this.

>>It may be that GROUP BY would give you what you want,
>>but you have to be careful with it because whilst aggregate
>>values will be correct simple row values rely on the physical
>>order of the table. This is often no problem but also often
>>give daft results

My example should have included a GROUP BY clause, and the "draft results" you mention are my exact problem!

Take the rows

ID, Value, DateDone, Text
1,  1, 01/01/2021, abc
1,  2, 01/02/2021, xyz
1,  3, 01/03/2021, dorayme

If I write
SELECT
 ID,
 MAX(DateDone),
 Value,
 Text
FROM Table
GROUP BY ID

I would expect to retrieve

1, 3, 01/03/2021, dorayme

ie the whole of the row with the MAX(DateDone)

But this is not what happens. You can get

1, 1, 01/03/2021, abc
or
1, 2, 01/03/2021, xyz
or
1, 3, 01/03/2021, dorayme

ie you DO GET the MAX Date, but all the other rows are just "filled in" from any row the engine picks.

I WANT the "Value" data for the row with the MAX date. (ie answering the question, "what is the value of the customers most recent order")

The following works for very simple cases, but just hits a wall when you want to retrieve more complex data:

SELECT
 ID,
 DateDone,
 Value
FROM Table WHERE DateDone =
 (SELECT MAX(DateDone)
   FROM Table
   WHERE ID = ?)

Put an ID in for a customer and you DO get the row / rows of data for the max date.

But try to do this with extra dimensions in the data ... it is really hard to get a set of ALL customers' most recent order value.

OK I got there.

A sub-select can reference the main select:

SELECT
 PersonID,
 P.FullName as Customer,
 DateDone,
 P1.Name as Product,
 SUM(II.Value) as Value
FROM Invoices I
 LEFT JOIN InvoiceItems II ON II.InvoicesID = I.ID
 LEFT JOIN Products P ON P.ID = II.ProductsID
 LEFT JOIN People P ON P.ID = I.PersonID
WHERE DateDone > DATE '2020-01-01'
  AND DateDone = (SELECT MAX(DateDone)
                  FROM Invoices I1
                  WHERE I1.PersonID = I.PersonID)  -----this line I1 references I
  AND II.Value > 0
  AND P.Current = true
GROUP BY PersonID, P.Name

It also returns reasonably fast.

Adam


OK I slightly misunderstood the problem, but it demonstrates the problem with GROUP BY - aggregate functions are fine but row values are / can be pretty much random. Its a subject that's come up before in these newsgroups


Roy Lambert

Adam


Good solution. Mine without the extra information you've supplied would have been to have sunselects for the non-aggregate values in the SELECT clause

Roy Lambert

Dear Roy
>>Good solution. Mine without the extra information you've supplied
>>would have been to have sunselects for the non-aggregate
>>values in the SELECT clause

Yes I frequently do this:

SELECT
 <fields>
FROM (SELECT
           ID,
           SUM(xxx)
           FROM MyTable) as Table1
LEFT JOIN (SELECT
                   ID,
                   SUM(yyy)
                    FROM MyTable) as Table2 ON Table1.ID = Table2.ID

An essential bit of SQL knowledge IMHO.

I had never written a SUBSELECT that joined to the main SELECT in that way though, and it does work, which is great.

Don't laugh, but I partly posted it here so next time I have to do it, and have forgotten how, when I search these news-groups I will find the anwser!

This should work too...

SELECT
DateDone as MaxDate,
Value1,
Value2
FROM Table
WHERE SomeCondition
ORDER BY DateDone DESC
RANGE 0 TO 1

Adam

>Don't laugh, but I partly posted it here so next time I have to do it, and have forgotten how, when I search these news-groups I will find the anwser!

Why would I laugh about that - I often surprise myself by finding an old post of mine that answers a problem

Roy