how do you do this one?
I think you're going to have to use a subquery to solve this one. Don't see any way around it. Anyone else have a clever solution without subqueries? Mike Ballentine --------------------------( Forwarded letter 1 follows )--------------------- Date: Wednesday, 30 April 1997 7:29am From: adil@msil.sps.mot.com Subject: how do you do this one? Date:Wed, 30 Apr 1997 08:01:08 -0500 Reply-To: r53837@email.sps.mot.com Organization: Deja News USENET Posting Service Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-Id: <news.37324> 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