Snapshot query

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

Re: Snapshot query

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


On Sep 3, 1:15 pm, jrpfinch wrote:
> 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