[Solved] filtering last entry of each location

Creating tables and queries

[Solved] filtering last entry of each location

Postby msahines » Wed Dec 12, 2018 4:13 am

new here, did lots of searching and couldn't find the statement to find the answer my mind is looking for.

i have a database with fields as: StartDate, LocationID, EmployeeID, Equipment
all in a table named: Table1

i found the following code to filter the data, getting the max "StartDate" field and displaying the remainder fields. which works fine... as long as all the multiple location's latest start date is the same date, which it will not be.

Code: Select all   Expand viewCollapse view
SELECT * FROM "Table1" WHERE "StartDate" = ( SELECT MAX( "StartDate" ) FROM "Table1" )


can anyone point me to where to find something, or give me the code that will get me a query on getting the latest "StartDate" of each of the different "LocationID" 's please?
Last edited by robleyd on Wed Dec 12, 2018 5:55 am, edited 2 times in total.
Reason: Tagged [Solved] [robleyd, Moderator]
openoffice 4.1.5 on windows
msahines
 
Posts: 2
Joined: Wed Dec 12, 2018 3:23 am

Re: filtering last entry of each location

Postby FJCC » Wed Dec 12, 2018 5:02 am

How about
Code: Select all   Expand viewCollapse view
SELECT * FROM "Table1"  INNER JOIN
( SELECT MAX( "StartDate" ) AS "MAXDATE", "LocationID" FROM "Table1" GROUP BY "LocationID" ) AS "MAXDATEQ"
ON "Table1"."StartDate" = "MAXDATEQ"."MAXDATE" AND "Table1"."LocationID" = "MAXDATEQ"."LocationID"
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7230
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: filtering last entry of each location

Postby msahines » Wed Dec 12, 2018 5:44 am

exactly what i needed, all returning properly now. thank you very much!
openoffice 4.1.5 on windows
msahines
 
Posts: 2
Joined: Wed Dec 12, 2018 3:23 am


Return to Tables & Queries

Who is online

Users browsing this forum: Google [Bot] and 4 guests