-
Hi,
Can any one help me in finding the second latest date from the table. i.e. in emp table I've 3 rows.
1. 1st jan, 2002
2. 2nd Jan, 2002
3. 3rd Jan, 2002.
I want to get the second record. Plz. its urgent...
thanx
Devang
-
select * from
(select a.*, rank() over(order by hiredate) as rank_date
from emp a)
where rank_date=2
it's probably semi correct, you probably have to look at dense_rank function, I forgot now since I dont have the docs handy
-
for example:
Table = Emp
Column = Hire_Date (Date Type)
SELECT MAX(Hire_Date - 1) FROM Emp;
F.
-
eh? what if the second row is 2 days?
-
And:
VARIABLE getsecond varchar2(8)
DECLARE
v_Max date;
v_Second date;
BEGIN
SELECT MAX(HIRE_DATE) INTO v_Max from FF;
SELECT MAX(HIRE_DATE) into v_Second FROM FF WHERE HIRE_DATE != v_Max;
:getsecond := to_char(v_second, 'dd/mm/yy');
END;
I understood (or misunderstood) he wanted the second
last date, so if there are two or more second last dates,
so the date is only one.
03/02/02
02/02/02
02/02/02
01/02/02
the second last date is 02/02/02.
F.
-
to get the second min. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
to get the second max. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate desc) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
hth.
Cheers!
OraKid.
-
If you have Oracle without analytic function:
* to find second max date:
Code:
select max(hiredate) from emp
where hiredate<(select max(hiredate) from emp)
* to find second min date:
Code:
select min(hiredate) from emp
where hiredate>(select min(hiredate) from emp)
-
First of all thanx..do u have any doc on dense_rank and over. I dont understand how that works?
Thanx..
Originally posted by balajiyes
to get the second min. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
to get the second max. date use the following query:
select * from
(select a.*, dense_rank() over(order by hiredate desc) as rank_date
from emp a)
where rank_date = 2
and rownum < 2
hth.
-
do u have any doc on dense_rank and over
Here
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|