15,796,738 members
See more:
Hello All,
```processID   date
75          26-06-2023
18          27-06-2023
75          27-06-2023
89          27-06-2023
98          27-06-2023
18          28-06-2023
18          29-06-2023
75          29-06-2023
89          29-06-2023
98          29-06-2023```

in above table, I need maximum date when group of process id available(here group- process id=18,75,89,98) where all 4 id's are present in a group. if one less id is present then it should take max means latest date where all four id's present. means if I run query on 27th June then I should get max date as 27th June(as here all 4 process id's present), if I run query on 28th June, still I should get date as 27th June(as on 28th June we dont have all 4 process id's present, we have only process id=18 present.)how can I achieve this?

I tried: select max(date) from table where processID IN (18,75,89,98) but here it gives maximum of process id not considering all 4 process id's present or not. What to do?

What I have tried:

select max(date) from table where processID IN (18,75,89,98) but here it gives maximum of process id not considering all 4 process id's present or not. What to do?
Posted
Updated 7-Jul-23 5:45am
v2
Member 15627495 7-Jul-23 9:22am
```SELECT distinct(processID), MAX(date) FROM 'table' order by processID ASC

```

## Solution 2

You can do this with a CTE (or a temporary table or a table variable)
SQL
```;;;;with cte as
(
select [date]
from @tab
group by [date]
having COUNT(*) = 4
)
select max([date]) from cte
where [date] <= @rundate;```
The first part is ensuring that I only look at dates where all four processes are present.
The final part looks for the maximum date before the run date selected. I used
SQL
`declare @rundate date = '2023-06-28';`

Just for completeness - I set up your sample data as follows
SQL
```declare @tab table (processID int, [date] date);
insert into @tab (processID, [date]) values
(75,'2023-06-26')
,(18,'2023-06-27')
,(75,'2023-06-27')
,(89,'2023-06-27')
,(98,'2023-06-27')
,(18,'2023-06-28')
,(18,'2023-06-29')
,(75,'2023-06-29')
,(89,'2023-06-29')
,(98,'2023-06-29');```

v2
Hello @CHill60: thanks for solution but it is not working as expected as I have different process ID as well apart from 18,75,89,98, so I applied where clause to filter out along with your solution but it is not giving right results. I have done like this: but it didnt work.
with cte as
(
select [date]
from @tab where processID in(18,75,89,98)
group by [date]
having COUNT(*) = 4
)
select max([date]) from cte
where [date] <= @rundate;
CHill60 10-Jul-23 4:28am
I have just run your code and it worked perfectly - for a run date of 28/06 it returned 27/06 as expected
I have updated my question here, this is actual requirement as above query is not giving result what is expected for me.

https://www.codeproject.com/Questions/5365486/How-to-write-SQL-for-below-scenario

## Solution 1

Use a GROUP BY:
SQL
`SELECT ProcessID, MAX(DATE) FROM MyTable GROUP BY ProcessID`
SQL GROUP BY Statement[^] and it's probably worth havign a look here as well: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]