How to get using "where clause" to make the date cloest tot eh sysdate
I have a table with a date field
This table is linked to other tables. The query result has multiple records. I want to the query result with only one record, which is the date one the closest to the sysdate. For example,
table ABC
id date
123 1/5/2010 10:04:58 AM
123 1/3/2010 10:04:58 AM
123 1/4/2010 10:04:58 AM
The query result I want is
123 1/5/2010 10:04:58 AM
because this one is closest to the sysdate
How can I do that in query by using where clause
This table is linked to other tables. The query result has multiple records. I want to the query result with only one record, which is the date one the closest to the sysdate. For example,
table ABC
id date
123 1/5/2010 10:04:58 AM
123 1/3/2010 10:04:58 AM
123 1/4/2010 10:04:58 AM
The query result I want is
123 1/5/2010 10:04:58 AM
because this one is closest to the sysdate
How can I do that in query by using where clause
ASKER
No, I cannot use group by and Min function. Can you do this in where clause?
what is your aim? If you can explain in detail we may help you more..
ASKER
This table is one of the query tables. The query is very complicated. But some records in this table has multiple records with same id. Because of that, that will make the final query result has more than one records. But I just want one record. In order to do so, I have to select table ABC has one record.
you can use it like this...
SELECT *
FROM tableABC ABC
INNER JOIN (SELECT id, MAX(date) as date FROM tableABC GROUP BY id ) DRV ON ABC.id = DRV.id AND ABC.date = DRV.date
.......
ASKER
I am using Oracle. It seems there is no INNER JOIN expression
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This query will become view. I wonder if the group function can be accepted in the view? I am using Oracle 8i
group by operations can be used in views
ASKER
also, I am using
and ROWNUM = 1
it seems work to have one entry but how to know this is the one closest to the sysdate
and ROWNUM = 1
it seems work to have one entry but how to know this is the one closest to the sysdate
do you have date values bigger then the cuurent date in the column?
ASKER
what do you mean? example please
I mean the values inserted into the date column can be bigger than the curent date? related to the feature operations...
today -- 01.07.2009
at the moment can the date column include any value bigger than this one?
today -- 01.07.2009
at the moment can the date column include any value bigger than this one?
ASKER
No, cannot be bigger than the current date or today or system date
so the max in my query will give you the closest date that means the query will work correct.. You can use it...
ASKER
you have more simple and other ideas to do this?
with the given information NO
the attached code is sql server 2005 code,and hope it to help you
select top 1 * from tableABC order by getdate()-[date]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
all are correct
from tableABC
group by id