| Help in using Anlytical function for a requirement [message #397442] |
Fri, 10 April 2009 14:04  |
sandyrao
Messages: 4
Registered: April 2009
|
Junior Member |
|
|
Hi,
I have the following table.
EMP Table:
-----------
ENO NUMBER
EUniqNo VARCHAR2
CREATE_DT DATE
ENO EUniqNo CREATED_DT
--- ------- -----------
1 ABC123 01-FEB-2003
2 DEF543 06-MAR-2003
1 ABC123 12-FEB-2009
2 DEF543 09-AP-2009
10 XYZ987 10-APR-2009
Assuming that this table has millions of rows, I want to get the
get the EUniqNo for rows with created_dt older than 200 days.
I have the following query for that,
SELECT EUniqNo FROM EMP
GROUP BY EUniqNo
HAVING MAX(CREATED_DT) < (SYSDATE -200);
1) How do I achieve this using analytical function?
2) Will Oracle provide a better plan if analytical function?
Thanks.
|
|
|
|
| Re: Help in using Anlytical function for a requirement [message #397443 is a reply to message #397442] |
Fri, 10 April 2009 14:32  |
 |
Michel Cadot
Messages: 65088
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1) Yes.
2) No, above all if you have an index on id and date.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
