Re: how do you do this one?

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: : [snip] : 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. Adi, Sorry, to say, I don't think that this can be done in one SELECT statement. My suggestion would be to create the view with the computer number and the MAX date for that computer in your table. Ex: select comp_num, max(date) from table group by comp_num Then join your view and your original table together and you can pull out your status. select status from table, view where table.comp_num = view.comp_num and table.date = view.date --- Travis Rayhons trayhons@dordt.edu Dordt College Computer Services (712) 722-6299 (w) Programmer/Systems Administrator