Get Max(date) or latest date with 2 conditions or group by or subquery

Question

I only have basic SQL skills. I'm working in SQL in Navicat. I've looked through the threads of people who were also trying to get latest date, but not yet been able to apply it to my situation.

I am trying to get the latest date for each name, for each chemical. I think of it this way: "Within each chemical, look at data for each name, choose the most recent one."

I have tried using max(date(date)) but it needs to be nested or subqueried within chemical.

I also tried ranking by date(date) DESC, then using LIMIT 1. But I was not able to nest this within chemical either.

When I try to write it as a subquery, I keep getting an error on the ( . I've switched it up so that I am beginning the subquery a number of different ways, but the error returns near that area always.

Here is what the data looks like: 1 Here is one of my failed queries:

SELECT
   WELL_NAME,
   CHEMICAL,
   RESULT,
   APPROX_LAT,
   APPROX_LONG,
   DATE

FROM
   data_all
ORDER BY
   CHEMICAL ASC,
   date( date ) DESC (
SELECT
   WELL_NAME,
   CHEMICAL,
   APPROX_LAT,
   APPROX_LONG,
   DATE 
FROM
   data_all 
WHERE
   WELL_NAME = WELL_NAME 
   AND CHEMICAL = CHEMICAL 
   AND APPROX_LAT = APPROX_LAT 
   AND APPROX_LONG = APPROX_LONG,

LIMIT 2 
)

If someone does have a response, it would be great if it is in as lay language as possible. I've only had one coding class. Thanks very much.

Answer 1

Maybe something like this?

SELECT WELL_NAME, CHEMICAL, MAX(DATE)
FROM data_all
GROUP BY WELL_NAME, CHEMICAL

Answer 2

If you want all information, then use the ANSI-standard ROW_NUMBER():

SELECT da.*
FROM (SELECT da.*
             ROW_NUMBER() OVER (PARTITION BY chemical, name ORDER BY date DESC) as senum
      FROM data_all da
     ) da
WHERE seqnum = 1;