Hi All
I am having the a table(1 million) with data as follows:
Input:
Col1 Col2 Col3
1 1895 4/7/2007
2 1895 4/7/2007
3 1895 3/5/2005
4 6600 3/3/2007
5 6600 3/3/2007
6 6600 3/1/2006
so on
Now I would like to have my output as follows:
Desired Output:
Col1 Col2 Col3
1 1895 4/7/2007
4 6600 3/3/2007
I tried using the following query for one id.
select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
and A.Col2=1895
AND ROWNUM<2
This worked fine and I got the output for 1895
When I tried using this
select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
AND ROWNUM<2
on bulk data, it is giving only one record. I assume this is due to rownum<2.
Please share your ideas how can i apply that to a full table
I am having the a table(1 million) with data as follows:
Input:
Col1 Col2 Col3
1 1895 4/7/2007
2 1895 4/7/2007
3 1895 3/5/2005
4 6600 3/3/2007
5 6600 3/3/2007
6 6600 3/1/2006
so on
Now I would like to have my output as follows:
Desired Output:
Col1 Col2 Col3
1 1895 4/7/2007
4 6600 3/3/2007
I tried using the following query for one id.
select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
and A.Col2=1895
AND ROWNUM<2
This worked fine and I got the output for 1895
When I tried using this
select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
AND ROWNUM<2
on bulk data, it is giving only one record. I assume this is due to rownum<2.
Please share your ideas how can i apply that to a full table
Comment