Jump to content


Photo

Query display pbm


  • Please log in to reply
1 reply to this topic

#1 shan_cool

shan_cool
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationChennai,India

Posted 06 March 2006 - 06:49 AM

Hi All,
I have a query and it displays set of records.. but i hav a pbm wit it. there will b one or more records for a particular employee.. but i need to display a single record for each of them which has the maxmimum m.startdate.....

my query is

select e.Emp_Id,e.Emp_FName,e.Emp_LName,e.Emp_Ug_Deg,e.PG_Deg,e.Emp_Desgn,e.Emp_DOJ,e.Base_Location,e.Emp_Ug_Year,e.PG_Year,i.Emp_Id,i.Test_Exp,i.Dev_Exp,i.Other_Exp,p.Prj_Code,p.Project_Name,p.Client,p.Location,m.Prj_Code,m.Emp_Id,m.Start_Date,m.End_Date,m.cty from HR_Emp_Det e,skill_master i,tdm_proposal p,tdm_prj_mem_det m where e.Emp_Id=i.Emp_Id and i.Emp_Id=m.Emp_Id and p.Prj_Code=m.Prj_Code and e.Emp_Status <> 'R' order by m.Emp_Id,m.Start_Date Asc


no it shows as:
for example.....

101 |Sridevi |Senior Test Engineer |04-Apr-2003 |2.92 |2.66 |5.66| 1.25| 0.25| 7.16 |B.Sc.,MCA| 2001 |Available |India |Trade Finance - Polaris| Nil |Mumbai |01-Jun-2004 |31-Dec-2004

101 |Sridevi |Senior Test Engineer |04-Apr-2003 |2.92 |2.66 |5.66 |1.25| 0.25 |7.16| B.Sc.,MCA |2001 Available |India |Constact Contact Genesis |Nil |Bangalore |14-Feb-2005 |31-Aug-2005

BUt I nedd to display only

101 |Sridevi |Senior Test Engineer |04-Apr-2003 |2.92 |2.66 |5.66 |1.25| 0.25 |7.16| B.Sc.,MCA |2001 Available |India |Constact Contact Genesis |Nil |Bangalore |14-Feb-2005 |31-Aug-2005
because 14-Feb-2005 is greater.. tell me how to change my query

Rgds,
Shan.


#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 06 March 2006 - 07:53 AM

You need to join on a subquery that uses MAX(date) and GROUP BY. I will attempt to take a closer look tomorrow.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users