Reply to: SQL MAX

[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.

[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.

[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.

[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]

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 :-)

[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

[BrilloPad]

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

Far more maintainable.

Remember to use KISS methodology...

[LondonManc]

Very true. No I (intentionally) didn't. :-)

What I actually need in the final query is (for each batch type), the last one that has actually been deployed and all those that are yet to be deployed.

There are in fact various other tables where data is collected from in the real query I am trying to end up with.

But I have my start pointer thank you.

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

[ASB]

To exclude forward dates, you just need an extra condition

Add this line before the one that starts "group by...." :
where a1.deployment < getdate()

The query includes future dates by default because you didn't specify that there could be future dates

Very true. No I (intentionally) didn't. :-)

What I actually need in the final query is (for each batch type), the last one that has actually been deployed and all those that are yet to be deployed.

There are in fact various other tables where data is collected from in the real query I am trying to end up with.

But I have my start pointer thank you.

[LondonManc]

I will give that a go tomorrow and then worry about the union I will need to add all the records with forwards dates.

To exclude forward dates, you just need an extra condition

Add this line before the one that starts "group by...." :
where a1.deployment < getdate()

The query includes future dates by default because you didn't specify that there could be future dates

[ASB]

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

I will give that a go tomorrow and then worry about the union I will need to add all the records with forwards dates.

[LondonManc]

SQL server 2008.

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

[ASB]

What RDBMS are you on, Oracle, SQL Server, Teradata?

SQL server 2008.

[ASB]

I had tried that (or at least what I believe to be logically the same) but it still returns each individual row.

Annoyingly of course I could easily just return the entire data set and process that in the consumer but I'm not supposed to do that.

[LondonManc]

I wish I didn't have to write this with SQL. But never mind.

I have a table:-

Deployment.

It contains:-

batch id (int)
deployment (datetime)

e.g.

1,13/09/2015
2,14/09/2015
3,15/09/2015
4,10/09/2015
2,11/09/2015
3,12/09/2015

Also:-

Batch.

Containing

batch id (int)
batch type(int)

I am trying to return the batch id, deployment, type

But I only want to return the most recent row for each batch type.

So I am completely stuck; mainly because I know stuff all about SQL. But the join would appear to need some sort of select max(deployment). No idea what though.

What RDBMS are you on, Oracle, SQL Server, Teradata?