Home » SQL & PL/SQL » SQL & PL/SQL » Help in using Anlytical function for a requirement
Help in using Anlytical function for a requirement [message #397442] Fri, 10 April 2009 14:04 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: Percentage of index rebuild
Next Topic: data to excel
Goto Forum:
  


Current Time: Sun Dec 04 04:38:06 CST 2016

Total time taken to generate the page: 0.23327 seconds