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
wasabi3689Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
you can use the INNER JOIN in oracle.. it supports them... if you dont want to use then change the query like

SELECT *
FROM tableABC ABC,
      (SELECT id, MAX(date) as date FROM tableABC GROUP BY id ) DRV

            .......
WHERE ABC.id = DRV.id
  AND ABC.date = DRV.date
         .......
0
 
tigin44Commented:
select id, MIN(sysdate - date)
from tableABC
group by id
0
 
wasabi3689Author Commented:
No, I cannot use group by and Min function. Can you do this in where clause?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tigin44Commented:
what is your aim? If you can explain in detail we may help you more..
0
 
wasabi3689Author Commented:
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.
0
 
tigin44Commented:
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
		.......

Open in new window

0
 
wasabi3689Author Commented:
I am using Oracle. It seems there is no  INNER JOIN expression
0
 
wasabi3689Author Commented:
This query will become view. I wonder if the group function can be accepted in the view? I am using Oracle 8i
0
 
tigin44Commented:
group by operations can be used in views
0
 
wasabi3689Author Commented:
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
0
 
tigin44Commented:
do you have date values bigger then the cuurent date in the column?
0
 
wasabi3689Author Commented:
what do you mean? example please
0
 
tigin44Commented:
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?
0
 
wasabi3689Author Commented:
No, cannot be bigger than the current date or today or system date
0
 
tigin44Commented:
so the max in my query will give you the closest date that means the query will work correct.. You can use it...
0
 
wasabi3689Author Commented:
you have more simple and other ideas to do this?
0
 
tigin44Commented:
with the given information NO
0
 
FredTangCommented:

the attached code is sql server 2005 code,and hope it  to help you


select top 1 * from tableABC order by getdate()-[date]

Open in new window

0
 
Shaju KumbalathConnect With a Mentor Deputy General Manager - ITCommented:

select * from (
select id,dt_col,rank() over ( order by dt_col desc) rank from tableabc where dt_col<sysdate) where rank=1 ;
0
 
ravibhardwajConnect With a Mentor Commented:
If you don't want to use group by and min then you can use RANK function
example:
select id,date
from(
Select id,date,rank() over (order by date desc) rank
from ABC
)
where rank =1;
0
 
awking00Connect With a Mentor Commented:
See attached.
query.txt
0
 
wasabi3689Author Commented:
all are correct
0
All Courses

From novice to tech pro — start learning today.