Without group by in aggregate function-CodePudding

How can I also get the value of c.id that matches the maximum of p.prep_date in the following query?

Queries:

Select max(p.prep_date) pdt,p.pond_id 
from pond_prep_soils2 p, 
     crops c,
     ponds pd 
where c.pond_id=pd.id 
  and (p.prep_date<c.cycle_start_date 
       and p.prep_date<c.cycle_end_date) 
group by p.pond_id

I have tried the following:

Select max(p.prep_date) pdt,p.pond_id,c.id 
from pond_prep_soils2 p, 
    ponds pd 
  left join crops c on c.pond_id=pd.id 
where (p.prep_date<c.cycle_start_date 
       and p.prep_date<c.cycle_end_date) 
group by p.pond_id

CodePudding user response：

For your case you can use ORDER BY in combination with LIMIT, e.g.:

SELECT p.prep_date pdt, p.pond_id, c.id
FROM pond_prep_soils2 p, 
    crops c,
    ponds pd 
WHERE c.pond_id=pd.id 
    AND (p.prep_date<c.cycle_start_date 
    AND p.prep_date<c.cycle_end_date) 
ORDER BY p.prep_date DESC NULLS LAST LIMIT 1

CodePudding user response：

When you tell us what you tried, you should also tell us what went wrong with it.

When you say "matches the maximum of p.prep_date", I assume you mean the max within the group, not the global max (which is what clemens assumed). You can do this with the PostgreSQL-specific DISTINCT ON construct.

Select DISTINCT ON (pond_id), p.prep_date, pdt,p.pond_id, c.id 
from pond_prep_soils2 p, 
     crops c,
     ponds pd 
where c.pond_id=pd.id 
  and (p.prep_date<c.cycle_start_date 
       and p.prep_date<c.cycle_end_date) 
ORDER BY pond_id, prep_date desc

Now for each distinct pond_id, it will return the first row given by the ORDER BY. That will of course be one with the max prep_date within that pond_id. If there are ties, an arbitrary member of the tie will be returned.

(I also have a hard time believing your WHERE is correct, surely one of the inequalities should be reversed, but that wasn't what your question was about)