Re: how do you do this one? answers

First of all, I'd like to thank everyone who sent me help. Thanks! From the general response, it appears this problem I posed (at the bottom) cannot be solved without a subquery or a join with a view. I was hopting to avoid those two, because they have relatively high performance costs, but there's nothing to be done about that. The two answers I got were as follows: option 1: subquery select t1.computer, t1.status, t1.Date from your_table t1 wheret1.Date = (select max(t2.Date) from your_table t2 where t1.computer = t2.computer) In this option, the t1.Date = ... is equivallent to t1.Date in ... option 2: join with a view on itself create view logview on your_table as select computer,max(date) as date from your_table group by computer select t1.* from your_table t1, logview t2 where t1.computer=t2.computer and t1.date=t2.date In checking with "set explain on", it appears that the first option is slightly better in terms of performance than the second. That is, for Online 5; I suspect that in Online 7 this may not be so, because I think Online 7 handles views differently (does anyone know?) Anyway, thanks again for everyone's help. Adi. In article <862401076.14850@dejanews.com>, r53837@email.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. > > -- > ---------------------------------------------------------------- > Adi Lachman phone: (972) 9-9522721 > Motorola Semiconductor Israel email: r53837@email.sps.mot.com > ---------------------------------------------------------------- > > -------------------==== Posted via Deja News ====----------------------- > http://www.dejanews.com/ Search, Read, Post to Usenet -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet