sql passthru query - MAX

[dchristo]

In the following there can be more than one (uniq_id) for the caseno in
dbo.master. I need the max(uniq_id) or last per caseno. I can do it when I
am only using one table, but I cannot seem to figure it out when there is
more than one table involved.

SELECT dbo.inform.code1, dbo.inform.nmlong, dbo.inform.desc1,
dbo.master.uniq_id, dbo.master.caseno, dbo.master.clnam, dbo.master.state
FROM dbo.inform INNER JOIN dbo.master on dbo.inform.code1=dbo.master.clnam
WHERE DBO.INFORM.CD_TYPE = 'CL' AND DBO.MASTER.CLOSED = '0'

Any help would be appreciated.

 

[Tom van Stiphout]

On Fri, 13 Nov 2009 05:30:01 -0800, dchristo

One option is to add to the where-clause:
and dbo.master.uniq_id in (select max(uniq_id) from dbo.master group
by caseno)

-Tom.
Microsoft Access MVP

 

[dchristo]

That was fantastic, thank you so much.

On Fri, 13 Nov 2009 05:30:01 -0800, dchristo

One option is to add to the where-clause:
and dbo.master.uniq_id in (select max(uniq_id) from dbo.master group
by caseno)

-Tom.
Microsoft Access MVP


.