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.

Tag your question with the database you are using. – Gordon Linoff Jul 10 '17 at 22:38 — Gordon Linoff, Jul 10 '17 at 22:38

score 1 · Answer 1 · answered Jul 10 '17 at 22:28

1

Maybe something like this?

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

answered Jul 10 '17 at 22:28

Chengzhi

2,531
2
27
41

Thank you very much, but what that returns is a single highest value (date) for each chemical, rather than for each well_name for each chemical. It is a very big differences in the number of results. – Yeye Jul 10 '17 at 22:33
@Yeye, can you please show some examples on how you would expect the result to be? – Chengzhi Jul 10 '17 at 22:37
I think his answer does give you the max data by Well_Name and Chemical. Can you post the exact query that you ran? You may be mistaking holes in your data for problems with your query. – M T Head Jul 10 '17 at 22:46
Yes, thanks. It will take me a minute to create, but I would be glad to. Also MT Head, thank you, I will run Chengzhi's suggested query. But it looks almost exactly like one I ran earlier. – Yeye Jul 10 '17 at 22:47
Looking at how it ran, I think you might be right. Apologies, Chengzhi. – Yeye Jul 10 '17 at 22:59
Yes, now I can see you were right. Your answer is so short and elegant. Thank you again. This was so helpful. – Yeye Jul 10 '17 at 23:26

score 0 · Answer 2 · answered Jul 10 '17 at 22:37

0

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;

answered Jul 10 '17 at 22:37

Gordon Linoff

1,242,037
58
646
786

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

2 Answers2