Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Finding data for the "latest" entry in a table |
Thu, Dec 16 2021 7:30 AM | Permanent Link |
Adam Brett Orixa Systems | 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 ... |
Thu, Dec 16 2021 9:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Dec 17 2021 5:54 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Fri, Dec 17 2021 6:12 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Fri, Dec 17 2021 7:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Dec 17 2021 7:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Dec 17 2021 9:39 AM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Fri, Dec 17 2021 8:45 PM | Permanent Link |
Mario Enríquez Open Consult | This should work too...
SELECT DateDone as MaxDate, Value1, Value2 FROM Table WHERE SomeCondition ORDER BY DateDone DESC RANGE 0 TO 1 |
Sat, Dec 18 2021 5:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |