Table1
Table1.Date of Last Sale
Table2
Table2.Firstname
Table2.Lastname
In english I have...
09/01/12 John Smith
04/11/14 John Smith
07/22/13 John Smith
11/30/11 John Smith
06/03/60 Fred Jones
09/01/12 Fred Jones
01/03/10 Fred Jones
10/25/12 Fred Jones
..and so on. Multiple people having placed multiple orders on multiple dates. The outcome I'm looking for is this:
04/11/14 John Smith
10/25/12 Fred Jones
I need the latest line item for all of John Smith's purchases, based on the "Date of Last Sale" column, same for Fred Jones, same for each person in the list.
I'm not a programmer but I do have some experience in Crystal Reports so I'm fairly confident I have the tables linked properly with the correct connectors, etc. What has me stumped is what a formula that returns, "only the line item with the latest "Date of Last Sale" for all orders with John Smith's name on them, then does the same for each person in the list looks like. Spent numerous hours on this already and all I can conclude is that "Maximum" may have something to do with it but that's as far as I've gotten.
Again, if I can build this in the Query Design window that would make things much smoother for me on my end. Anybody? Thanks in advance for your help.
How To Select Record With The Latest Date?
-
- Posts: 1
- Joined: Sun Sep 27, 2015 6:58 pm
How To Select Record With The Latest Date?
OpenOffice 4.1.1 on Windows 8.1
Re: How To Select Record With The Latest Date?
To be able to solve this in SQL you need to think in data sets. Unfortunate you did not give me your table structure so I've to describe it a little in pseudo code, but I hope you grasp the idea.
Make a query that returns the last date per person, save that query as qLastSale:
You can now use this query as a basis for your other selections queries.
To help any further please upload a base document with some sample data.
Make a query that returns the last date per person, save that query as qLastSale:
Code: Select all
SELECT MAX(a."Date of Last Sale") as "LastSaleDate", b.Firstname, b.Lastname
FROM Table1 a INNER JOIN Table2 b ON a."forignKeyToTable2" = b."primaryKeyTable2"
GROUP BY b.Firstname, b.Lastname
To help any further please upload a base document with some sample data.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: How To Select Record With The Latest Date?
This is a really useful code that I would like to adopt for my own database project. However, I need to expand it a little and cannot figure out how. Maybe someone could help me with this.
I wish to add to my a query result a "comment" which is stored in the same table as the dates of the purchase.
Like This:
Table a: 09/01/12 "Ordered two apples" - Related Table b: John Smith
Table a: 04/11/14 "Ordered one lemon" - Related Table b: John Smith
Table a: 07/22/13 "Ordered three apples" - Related Table b: John Smith
Table a: 11/30/11 "Ordered one banana" - Related Table b: John Smith
So that the result of my query is
04/11/14 John Smith "Ordered one lemon"
10/25/12 Fred Jones "Ordered a car"
...
I would be grateful for any help!
Michael
I wish to add to my a query result a "comment" which is stored in the same table as the dates of the purchase.
Like This:
Table a: 09/01/12 "Ordered two apples" - Related Table b: John Smith
Table a: 04/11/14 "Ordered one lemon" - Related Table b: John Smith
Table a: 07/22/13 "Ordered three apples" - Related Table b: John Smith
Table a: 11/30/11 "Ordered one banana" - Related Table b: John Smith
So that the result of my query is
04/11/14 John Smith "Ordered one lemon"
10/25/12 Fred Jones "Ordered a car"
...
I would be grateful for any help!
Michael
LibreOffice 5.2.6.2 on MAC
Re: How To Select Record With The Latest Date?
The example query is about finding the last date of table a. You have to join again on the same table relation to get your comment:
I assume that you saved your query as 'qLastSale' Introduce a query like 'qAllSale':
Now you can combine the two:
This will break when you have more that one sale at the same date of the same (b.Firstname, b.Lastname).
I assume that you saved your query as 'qLastSale' Introduce a query like 'qAllSale':
Code: Select all
SELECT a."Date of Sale" as "SaleDate", b.Firstname, b.Lastname
FROM Table1 a INNER JOIN Table2 b ON a."forignKeyToTable2" = b."primaryKeyTable2"
Code: Select all
SELECT a."LastSaleDate", b."Comment", a.Firstname, a.Lastname
FROM qLastSale a
INNER JOIN qAllSale b
ON a."LastSaleDate" = b."SaleDate" and a.Firstname = b.Firstname AND a.Lastname = b.Lastname
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Re: How To Select Record With The Latest Date?
Great! The code works well and I learned something new.
Thank you very much. You just helped a small community-housing project to become better organized.
Best regards,
Michael
Thank you very much. You just helped a small community-housing project to become better organized.
Best regards,
Michael
LibreOffice 5.2.6.2 on MAC