SQL MAX

[ASB]

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.

 

[Underbase]

Something like

Select b.BatchId, max(d.Deployment), b.BatchType From Deployment d
Inner join Batch b on b.BatchId=d.BatchId
Group by b.BatchId,B.BatchType

?

 

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

 

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

 

[ASB]

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

SQL server 2008.

 

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

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]

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]

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]

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