select the last modified line for each item

[yakata]

Hi there,

So stupid I think, I just have a table with a list of devices and the date of their last check.

I need to list only one line per device which is the last check date.
Help me, i'm stuck, tryin to use max but I can't figure out how to deal with the distinct and group by clauses...

TIA for a reply

 

[jbenson001]

Select device, Max(<date col>)
From <table>
Group By device

 

[kstickler]

I basically have a similar issue as Yakata...except that I also want the other fields of the record to be in the result. When I try to do this, it wants to use some sort of aggregate function on each field. I figure that once it has grouped, then found the max of a particular field, it should be easy to pull in the other fields on that same record. I do this all the time usng a report writer...but MS Query has me stuck!

 

[amrita418]

Try this:

select        a.* 
from          table a,
              (select     key, 
                         max(updatedate) 'max_date'
              from       table
              group by   key) b
where         a.key = b.key
              and a.updatedate = b.max_date

Regards,
AA