Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #442338] Sun, 07 February 2010 01:17 Go to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi

i am not able to write a query to select particular rows.
I have table which has data like below :

EMPLOYEE_NUMBER EFFECTIVE_DATE
00003122 1/1/1995
00003122 3/31/1999
00003122 4/1/1999
00003122 11/1/1999
00003122 5/1/2000
00003122 11/1/2000
00003122 1/31/2002
00006131 1/1/1995
00006131 3/31/1999
00006131 4/1/1999
00006131 4/1/2000
00006131 1/15/2002
00028550 5/1/2006
00028550 6/1/2006
00028550 3/18/2008
00028550 6/15/2009
00028550 11/7/2009
00028550 4/29/2011

I want to select only those employees which have the latest effective_date.

Please help to provide me the SQL.

regards
-navin
Re: SQL query [message #442342 is a reply to message #442338] Sun, 07 February 2010 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: SQL query [message #442361 is a reply to message #442338] Sun, 07 February 2010 07:23 Go to previous messageGo to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member

through order by and rownum
Re: SQL query [message #442457 is a reply to message #442338] Mon, 08 February 2010 04:19 Go to previous message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Thanks All for your reply..

after some hit and trial, i was able to find one query.

select * from emp a where a.employee_number in ('00003122','00006131','00028550') and a.effective_date = (select max(b.effective_date) from emp b where b.employee_number= a.employee_number);

regards
-navin
Previous Topic: Procedure to convert the sql output or cursor to dbf file
Next Topic: REF CURSORS
Goto Forum:
  


Current Time: Sat Oct 01 12:47:56 CDT 2016

Total time taken to generate the page: 0.16987 seconds