Snapshot query

[jrpfinch]

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 autoincrementin g
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.s tatus' 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]

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 <jrpfi...@gmail .comwrote:

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 autoincrementin g
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.s tatus' 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