I unsuccessfully attempted to leverage Java's DerivedQueries but cannot accomplish the required result so I have to manually write a SELECT Statement.
I want to display one single record in my UI. This should be the most recently generated record (which means it has the highest ID Number) associated with a category that we call "ASMS". In other words, look through all the rows that have ASMS#123, find the one that has the highest ID and then return the contents of one column cell.
ASMS: Entries are classified by 11 specific ASMS numbers. ID: AutoGenerated PPRECORD: New entries being inserted each day
I hope the image makes more sense.
//RETURN ONLY THE LATEST RECORD
//https://besterdev-api.apps.pcfepg3mi.gm.com/api/v1/pprecords/latest/{asmsnumber}
@RequestMapping("/pprecords/latest/{asmsNumber}")
public List<Optional<PriorityProgressEntity>> getLatestRecord(@PathVariable(value = "asmsNumber") String asmsNumber) {
List<Optional<PriorityProgressEntity>> asms_number = priorityprogressrepo.findFirst1ByAsmsNumber(asmsNumber);
return asms_number;}
The ReactJS FE makes an AXIOS.get and I can retrieve all the records associated with the ASMS, but I do not have the skill to display only JSON object that has the highest ID value. I'm happy to do this in the FE also.
I tried Derived Queries. .findFirst1ByAsmsNumber(asmsNumber)
does not consider the highest ID number.
Try this:
SELECT pprecord FROM YourTable WHERE id =
(SELECT MAX(id) FROM YourTable WHERE asms = '188660')
Explanation:
First line select pprecord, second line select the id
I'll improve the answer if any additional question. Upvotes and acceptions are appreciated~