Snapshot query -- sql server (alternate)

jrpfinch
9/3/2007 1:15:08 AM

Hi

I am relatively new to databases. I would like to be able to run a
query that returns the t_no, b_no, status and cpu for the latest
record for each unique combination of (t_no, b_no and cpu) before a
given point in time (say @snap_time). d_no is an autoincrementing
primary key.

d_no t_no b_no status cpu update_time
1 500 3 0 1 3:01
2 501 3 1 1 3:02
3 501 3 0 1 3:03
4 502 3 1 1 3:04
5 503 3 1 1 3:05
6 500 3 1 1 3:10

In this example, if @snap_time = '3:15' the query would return the
(t_no, b_no, status, cpu) for records 3, 4, 5, 6 because 1 is
superceded by 6 (which has the same t_no, b_no, cpu combination but
occurs later) and 2 is superceded by 3.

If @snap_time = '3:03', the query would return records 1 and 3 because
records 4, 5 and 6 were created at a later update_time and record 3
supercedes record 2.

The query:

SELECT MAX(update_time), t_no, b_no, cpu, status
FROM my_table
WHERE (time <= @snapTime)
GROUP BY t_no, b_no, cpu

returns an error:

Column 'dbo.my_table.status' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.

And even if this did work, ideally I would like to suppress the
update_time field from appearing.

How could I do such a query and is it even possible without using
something more sophisticated than a select query?

Many thanks

Jon

Shiju Samuel
9/3/2007 2:07:35 AM

What is the datatype for updatetime? I am assuming it as Datetime.

;with cte as(
SELECT MAX(update_time) update_time, t_no, b_no, cpu, status
FROM my_table
WHERE (time <= @snapTime)
GROUP BY t_no, b_no, cpu, status
)
SELECT t_no, b_no, cpu, status
from cte a inner join my_table b
on
a.t_no = b.t_no and
a.b_no = b.b_no and
a.cpu = b.cpu and
a.status = b.status and
a.update_time = b.update_time

-
Shiju Samuel

[quoted text, click to view]

sql server (alternate) : Snapshot query