Retreiving latest record 2

[RiverGuy]

Here is what I have:

Select ID, Field1, max(DateField) From Table1.

What I want is the latest record for each ID. I still get every record for each ID.

Thanks in advance.

 

[PruSQLer]

It should be:

Select ID, Field1, max(DateField)
From Table 1
Group by ID, Field1

I'm surprised your SQL editor let you run it without the Group By clause.

 

[RiverGuy]

Actually I did have group by in there. Forgot to type it in (using a dumb terminal, no copy/paste).

Actually what I think it is doing, is giving me the max date with each distinct Field1. So if there happen to be two same instances of Field1 with the same ID, it will give me one record. But I just want the last record for each ID.

 

[PruSQLer]

Yup, each unique value of Field1 will cause another row to show up on the results. Of course, the easy thing to do is take out Field1 But, if you need Field1 in there, I think this might work:

with temp
(id1, maxid)
As (
Select ID, max(Field1)
from Table1
Group by ID)
Select ID, maxid, max(DateField)
from temp,
Table1
where Table1.ID= id1
group by Table1.ID, maxid

This uses a Table Expression so I hope you can use those in your version of DB2. I just did one little test on this but it seemed to work. Hope this helps.

 

[RiverGuy]

Thanks, I will give it a try and let everyone know how it works out.

 

[RiverGuy]

Hey PruSQLer. Sorry to get back to you so late. Thanks for that. It does work. I got my data, although it runs slow, so I'm not sure if they are going to want me to use it, but that is really cool code to have.

 

[RiverGuy]

Actually, I take that back, it is not too slow. I was accidentally getting the max date for the temp table instead of Field1.

 

[lynchg]

I am looking to do something similar, but I am getting a syntax error when I run PruSQLer's code. Here is my SQL:

with temp
(id1, maxMeasure)
As (Select CORP_MBR_ID, max(gpi_code)
from dba1.drug_claim
where left(gpi_code,4) = '4410'
Group by CORP_MBR_ID)
Select CORP_MBR_ID, maxMeasure, max(SRVC_FROM_DATE)
from temp,
dba1.drug_claim
where dba1.drug_claim.CORP_MBR_ID = id1
group by dba1.drug_claim.CORP_MBR_ID, maxMeasure

The error message is:

An unexpected token "(" was found following "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING>", Expected tokens include "", SQLSTATE: 42601.

I am using AQT 6.1.6 with DB2 on a mainframe.

 

[PruSQLer]

Does your platform allow you to use Table Expressions? I don't see anything wrong with the syntax.