Re: how do you do this one?
Why shun a subquery, when it is fairly straightforward, depending on what
indexes your table maintains?
select x.computer, x.status, x.date
from logtable x
where x.date in (
select max(y.date)
from logtable y
where y.computer = x.computer
)
This structure operates what amounts to a correlated subquery.
You may be able to substitute an "=" for the "in".
An alternative is to create a view for the max date values, then join to
the view:
'have available the view
create view latest_stat_date as
select computer, max(date) date
from logtable;
'run-time select, which can also be given a view name as desired
select lt.*
from logtable lt, latest_stat_date lsd
where lt.computer = lsd.computer
and lt.date = lsd.date
How's that? Good luck. MI
Melvin A. Ivey, Asst Prof of MIS | voice: 541-885-1346
Oregon Institute of Technology | fax: 541-885-1687
3201 Campus Drive, MS OW143 | http://internet.oit.osshe.edu/~iveym
Klamath Falls, OR 97601-8801 | The Purpose of Education is *Action*
On Wed, 30 Apr 1997 adil@msil.sps.mot.com wrote:
> I though writing a query for the following problem would be easy.
> Wrong! I couldn't find how to do it. Can anyone help?
>
> Let's say I have a table which represents a status log for a list
> of computers. The log contains the id of the computer, the status
> of the computer (say, "up and running","in repair", etc), and the
> date the status was changed. For each computer, there are several
> entries with different status types, something like this:
>
> computer status Date
> -------- ----------------------- --------
> 1103 Arrived from store 12/01/96
> 1103 Installed 15/01/96
> 1103 Stopped Working 22/07/96
> 2171 Installed 04/08/95
> 3133 Stopped Working 17/12/96
> 3133 Repaired 03/01/97
>
>
> And so on. What I want to get, is the latest status of each computer,
> i.e.
>
> computer status Date
> -------- ----------------------- --------
> 1103 Stopped Working 22/07/96
> 2171 Installed 04/08/95
> 3133 Repaired 03/01/97
>
> I want to do this in a single select, without having to create
> a temporary table or do a subquery (i.e. use "in") for this. Actually
> I would idealy want to put this in a view, if possible.
>
> Any ideas?
>
> Thanks,
> Adi.