Posted by jrpfinch on 09/03/07 08:15

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

[Back to original message]