Max of UniqID in Sql

[dchristo]

SELECT dbo.inform.code1, dbo.inform.name, 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 = 'CO' AND DBO.MASTER.CLOSED = '0'
ORDER BY dbo.master.caseno

My problem is there can be one case number with two Uniq_id's, I need to get
the max of the Uniq_ids. I can do it when I only have one table to work
with, but I can't quite understand when I have two or more tables to work
with.

Thank you in advance.

 

[Daryl S]

Dchristo -

I added an AND statement that will restrict fir uniq_id to be the max one
for the caseno. The difference in the two SQL statements is the AS in making
the alias master2. Depending on your back-end it could go either way.

SELECT dbo.inform.code1, dbo.inform.name, 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 = 'CO' AND DBO.MASTER.CLOSED = '0'
AND dbo.master.uniq_id = (select max(master2.uniq_id) from dbo_master
master2 where master2.caseno = dbo.master.caseno)
ORDER BY dbo.master.caseno

SELECT dbo.inform.code1, dbo.inform.name, 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 = 'CO' AND DBO.MASTER.CLOSED = '0'
AND dbo.master.uniq_id = (select max(master2.uniq_id) from dbo_master AS
master2 where master2.caseno = dbo.master.caseno)
ORDER BY dbo.master.caseno

Hope that helps!