Search code examples
javareactjspostgresqlaxiosderived

Simple SQL query on highest ID

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.

Requirement

I tried Derived Queries. .findFirst1ByAsmsNumber(asmsNumber) does not consider the highest ID number.

Solution

  • 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~