We help IT Professionals succeed at work.

distinct and max sql query

second_base
second_base asked
on
I am trying to create a query to get the max or min date for an individual record.

A subset of the table definition is below:

dttm                     element_number    
05-01-2001 00:00:00.000  63                
06-01-2001 00:00:00.000  63                
07-01-2001 00:00:00.000  63                
05-01-2001 00:00:00.000  71                
06-01-2001 00:00:00.000  71                
07-01-2001 00:00:00.000  71                
08-01-2001 00:00:00.000  71                
05-01-2001 00:00:00.000  85                
06-01-2001 00:00:00.000  85                
06-01-2001 00:00:00.000  99                

I have tried to use the distinct statement, but that will get each record.  I have tried to use the max function, but I need both the dttm and element_number and using the max function I need both dttm and element_number and I am forced to aggreate(group) both dttm and element_number and results are the same.

I have tried to do a sub-query inside a query.  I have tried using a inner join.  I have tried queries using both distinct and the max function.  However, these attempts have failed to produce the results I need.

I would like the query to return the following results based on the table defintion listed above using the max value for dttm(datetime).

dttm                     element_number    
07-01-2001 00:00:00.000  63                
08-01-2001 00:00:00.000  71                
06-01-2001 00:00:00.000  85                
06-01-2001 00:00:00.000  99

The bottomline is I need to find out which elements are not reporting in a timely fashion.

Thanks.
               
Comment
Watch Question

Commented:
What database are you using?

Author

Commented:
I am using MS SQL Server 7.0.
select dttm, element_number
from tbl
where dttm = (select max(dttm) from tbl t2 where t2.dttm = tbl.dttm)

or you could

select dttm = max(dttm), element_number
from tbl
group by element_number

Author

Commented:
The first query does not work.  The second query works fine.

I do have an additional request.  How would I make the query to get only records that are older than two weeks?

I tried the query below, but it listed all entries for each element_number older than 15 days.

select dttm = max(dttm), element_number
from tbl where dttm <= dateadd(day, -15, getdate())
group by element_number

Tell me how you want to do this?  New question and close out existing question?  I am willing to additional points.

Thanks.

Author

Commented:
The first query does not work.  The second query works fine.

I do have an additional request.  How would I make the query to get only records that are older than two weeks?

I tried the query below, but it listed all entries for each element_number older than 15 days.

select dttm = max(dttm), element_number
from tbl where dttm <= dateadd(day, -15, getdate())
group by element_number

Tell me how you want to do this?  New question and close out existing question?  I am willing to additional points.

Thanks.

sorry the first should have been
select dttm, element_number
from tbl
where dttm = (select max(dttm) from tbl t2 where t2.element_number = tbl.element_number)


Not sure what your problem is here - what you have should be very close - for 2 weeks

select dttm = max(dttm), element_number
from tbl
where dttm <= dateadd(ww, -2, getdate())
group by element_number

or maybe

select dttm = max(dttm), element_number
from tbl
where dttm < dateadd(ww, -2, convert(varchar(11,getdate(),113))
group by element_number

From your description it sounds like you didn't include the group by when you ran it.

Author

Commented:
I was not able to get the queries for only devices that have not been updated in older than two weeks to work.

I will try a couple of different things.

Thanks for your help.
If you post what you are expecting and what you get maybe I could help (tomorrow).

Explore More ContentExplore courses, solutions, and other research materials related to this topic.