second_base
asked on
distinct and max sql query
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.
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.
What database are you using?
ASKER
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
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
ASKER
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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).