0

there are more then two rows of a single employee based on date(say effdate) in table and I have to select the most recent row for that employee. please suggest a sql query For example if a table contains scott 22/12/1980 rambo 23/12/1990 spawn 25/12/1991 scott 22/12/2000 rambo 23/12/1970 spawn 25/12/1999

i want answer like Scott 22/12/2000 rambo 23/12/1990 Spawn 25/12/1999

Amit
  • 15
  • 4

1 Answers1

1

Try this !

In sql-server

select top 1 * from table order by emp_date desc

In my-sql

SELECT *
FROM table 
ORDER BY emp_date DESC
LIMIT 1

in oracle

SELECT *
  FROM (SELECT * FROM table ORDER BY  emp_date)
 WHERE ROWNUM = 1;

After seeing the Updated question :

In sql-server

select * from( 
select *,rn=row_number()over(partition by name order by date1 desc) from table
)
where rn=1
vhadalgi
  • 7,027
  • 6
  • 38
  • 67