If I had a table like the following:

create table org(
id numeric(10,0) identity,
org_no char(6) not null,
org_name varchar(20) not null,
effective_dt datetime not null,
primary key (id)
)

and it contained the following data
1 100 Org #1 1/1/2003
2 200 Org #2 1/1/2003
3 300 Org #3 1/1/2003
4 400 Org #4 1/1/2003
5 100 Org #1a 1/1/2004
6 200 Org #2a 1/1/2004
7 300 Org #3a 1/1/2004

Basically the data reflects the org_name value changing at the
begining of the year for 3 of the 4 organizations so we now have two
entries for some organizations. I need a SQL that will retrieve only
the "latest" record for each organization. The end result set should
be:

4 400 Org #4 1/1/2003
5 100 Org #1a 1/1/2004
6 200 Org #2a 1/1/2004
7 300 Org #3a 1/1/2004

Is there a simple way to do this? Obviously the above is a simplified
form of the real table which can have hundreds of records for the same
organization but only one record is the "latest".

Thanks,

James K.

James Knowlton wrote:
[clip clip]
select *
from org
group by org_no
having effective_dt = max(effective_dt)

You should also think about storing information for each row telling that
is it the latest row if:

a) updates happens seldom compared to reads
b) you always fetch only the latest rows (not for example the ones that
were latest at 1.1.2001)

Indexing this 'latest' -column will result to better performance because
no grouping is needed (and not all of the rows are read). In this case it
is still possible to fetch also old rows, but group by + having is needed
then.

You could also think about creating separate table for old data and move
old row to there when new row is created.

Toni

jlknowlton (AT) hotmail (DOT) com (James Knowlton) writes:

select o.org_no,
o.org_name
from org o
where not exists (
select *
from org newer
where o.org_no = newer.org_no
and o.effective_dt < newer.effective_dt
)

I've sometimes written the same thing using temp tables rather than a
subquery:

select org_no,
max(effective_dt) as max_dt
into #org_max_dt
from org
group by org_no

create unique clustered index idx
on #org_max_dt (
org_no
)
go

select o.org_no,
o.org_name
from org o,
#org_max_dt m
where o.org_no = m.org_no
and o.effective_dt = m.max_dt

Whether this is faster or not I do not know - I think it would depend
on whether you have an index on (org_no, effecitve_dt) and the speed
of operations in tempdb - but it appeals to some strange sense of
tidiness in building the result up one step at a time. If performance
were important I would benchmark the two and compare, of course.

--
Ed Avis <ed (AT) membled (DOT) com>

Toni Salomäki <tsalomak (AT) hotmail (DOT) com> writes:

Zoiks! This is much simpler than using a subquery, which I had
assumed was necessary. Your way works becuase of Sybases's
rather unusual 'group by' where you can select non-aggreated columns
that aren't included in the group by list. Until now I had shied away
from that feature, not realizing there was at least one good use for
it.

--
Ed Avis <ed (AT) membled (DOT) com>