Selecting latest date in a range

Hi Peter and apologies for taking so long to reply.

Ive used your example above and made some progress however, this only returns the most recent record (by date) rather than showing the most recent record from each individual.

The custom SQL queries is as you have described above (typed into the ‘where’ field in Data Designer). I wasnt sure whether there is another field where you can add in custom SQL queries to manipulate the data prior to using it in a child page as in;

Main data table —> Custom queries —> Custom data Table for use in Data Project —> data used in Page

Thanks Peter. With multiple WHERE clauses, what separator should I be using?

Ive tried colon/semicolon, comma, space but none seem to work?

I would like the WHERE phrase to be;

Date_Trained >= (select max(Date_Trained) from wp_database), Candidate_Name = “Xxxx X”, Candidate_Name = “Yyyy Y”, Candidate_Name = “Zzzz,Z”

Hi Peter, the above syntax returns no results.

Im trying various versions of this now but….

…using this code;
Date_Trained >= (select max(Date_Trained) from wp_database) where Candidate_Name = “Xxxx X”

gives me everyones records from the max(Date_Trained) record of Candidate_Name = “Xxxx X”

So, if I have the following records;
NAME Latest Record
Xxxx X 2018/10/06
Yyyy Y 2017/09/23
Zzzz Z 2018/11/15

the above code only returns Xxxx and Zzzz records and doesnt include Yyyy which would make sense as the date has been set by Xxxx latest record.

What I am after is everyones latest record.

Thanks, jeremy

Thanks Peter, but ive tried the OR and it then finds the person who has the most recent entry only :-/

This SQL statement returns the correct results;

SELECT Candidate_Name, MAX(Date_Trained) AS most_recent_date FROM wp_dbnamexxx GROUP BY Candidate_Name

The difference here is that it is finding the name first and then finding the most recent record (and then groups by name)

the problem with the current code is that it looks for the most recent date and then stops and returns a name (or based on a name if the “” where Candidate_Name = ‘Xxxx X’ “” statement is used. I am tryng to obtain everyone’s most recent date.

Im still trying to find a way so your continued help is invaluable

Thanks, Jeremy

Hi Peter, using the view coding (Import scripts through Data Explorer) with an SQL query has worked and displays via Data Pub just as I wanted so thanks very much for your help.