I have two tables, one contains ids(very small, less than 100) and the other contains history(very large, over 1000 mil);
table a (id int)
table b (id int, contact_datetime datetime, id2 int, id3 int)
I need to pull latest contact date and other id fields from table b fastest possible.
in 2000, I did following sql query,
select a.id, b.contact_datetime, b.id2, b.id3
from table a
inner join table b on b.id = a.id
where b.contact_datetime = (select max(contact_datetime) from table b where id = b.id)
Do you have any better ways to do this in 2005
How Can I Do This Better in SQL 2005?
Przemo2
That is probably the best way without modifying any code (assuming the contact_datetime is unique for each instance of id. Unless you don't mind > 1 row per id in these cases.
Commonly I will add a column like currentContact which is a bit where the last one is set to 1, the others 0. I set this with a trigger that either behaves like your query, or if the rows are inserted in order of creation (like a journal of some sort) just takes the last one.
So this query would be morphed to:
select a.id, b.contact_datetime, b.id2, b.id3
from table a
inner join table b on b.id = a.id
where b.currentContact = 1
To me it just depends on the cost of the operation. If your query runs fast enough, then your method is better because adding a column and trigger adds complexity. I will sometimes sacrifice complexity for performance, but not unless it is truly needed.
dazza70
Here's an alternative, (not necessarily better!)
WITH
Ranked_b(id,contact_datetime,id2,id3,rn)AS
(
SELECT
id,contact_datetime
,id2
,id3
,RANK
() OVER(PARTITION BY id ORDER BY contact_datetime DESC)FROM
b)
SELECT
a.id,b
.contact_datetime,b
.id2,b
.id3FROM
aINNER
JOIN Ranked_b b ON b.id = a.id AND b.rn=1karl_otto