How To Select Record With The Latest Date?

Discuss the database features

How To Select Record With The Latest Date?

Postby Altstatten » Sun Sep 27, 2015 7:53 pm

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.
OpenOffice 4.1.1 on Windows 8.1
Altstatten
 
Posts: 1
Joined: Sun Sep 27, 2015 6:58 pm

Re: How To Select Record With The Latest Date?

Postby eremmel » Sun Sep 27, 2015 10:05 pm

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:
Code: Select all   Expand viewCollapse view
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


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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 998
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: How To Select Record With The Latest Date?

Postby hoettilie » Thu Feb 15, 2018 11:16 am

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
LibreOffice 5.2.6.2 on MAC
hoettilie
 
Posts: 2
Joined: Thu Feb 15, 2018 11:05 am

Re: How To Select Record With The Latest Date?

Postby eremmel » Thu Feb 15, 2018 5:26 pm

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':
Code: Select all   Expand viewCollapse view
SELECT a."Date of Sale" as "SaleDate", b.Firstname, b.Lastname
FROM Table1 a INNER JOIN Table2 b ON a."forignKeyToTable2" = b."primaryKeyTable2"

Now you can combine the two:
Code: Select all   Expand viewCollapse view
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

This will break when you have more that one sale at the same date of the same (b.Firstname, b.Lastname).
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 998
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: How To Select Record With The Latest Date?

Postby hoettilie » Thu Feb 15, 2018 9:41 pm

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
LibreOffice 5.2.6.2 on MAC
hoettilie
 
Posts: 2
Joined: Thu Feb 15, 2018 11:05 am


Return to Base

Who is online

Users browsing this forum: No registered users and 7 guests