SQL MAX

[BrilloPad]

Personally I would use a temp table. Or at least heavily comment the sql.

Far more maintainable.

Remember to use KISS methodology...

[mudskipper]

Give this a try then:

select a.batch_id, a.deployment, b.batch_type
from deployment a join batch b on a.batch_id=b.batch_id
join
(select b1.batch_type, max(a1.deployment) maxdeploy
from deployment a1 join batch b1 on a1.batch_id=b1.batch_id
group by b1.batch_type) c
on b.batch_type=c.batch_type and a.deployment=c.maxdeploy

Including a deployed flag (You may need to change < to <= if today = deployed)

SQL Fiddle

[stryker007]

Am I missing something? because simply this seems to work fine.

select d.batch_id, d.deployment, b.batch_type from deployment d
left join batch b on d.batch_id = b.batch_id
group by d.batch_id
order by d.deployment DESC

oops.. albeit under mysql :-)

[LondonManc]

Am I missing something? because simply this seems to work fine.

select d.batch_id, d.deployment, b.batch_type from deployment d
left join batch b on d.batch_id = b.batch_id
group by d.batch_id
order by d.deployment DESC

oops.. albeit under mysql :-)

Yes, you're missing the bit where the OP only wants the top deployment for each batch type.

[stryker007]

That's what my query does as it orders by deployment descending and groups by batch_id.... I can't be sure that's the most efficient query at a glance but it yields the results.

[ASB]

You could do that as either a union query for the two types of event or use it as one daase expressions for each. I'd have to invoice you if I spent any longer on it though

You have been very helpful. I have also been diverted from it so it is tomorrow. I will use a union.

[ASB]

Thank you for all the help and suggestions.

Here is what I ended up doing....

Simple query to return all the data. Then write the c# code to mangle it to what I really want. It was just easier for my skillset that way.