Jump to content

Filtering the result of a query.....


shan_cool

Recommended Posts

Hi all,

I hav two set of rows for each employee.. but i need to show only one row for each employee based on the latest start date..... fo example i hav as

 

EmployeeName Project Name Start Date End Date

 

shan proj1 01-Jul-2002 03-Mar-2003

shan proj2 01-Feb-2003 31-Jul-2004

 

 

here i need to display only the second row.

 

my query is select EmployeeName, ProjectName,max(StartDate),EndDate frm emp_prj group by empid

 

 

but is mixing up the two rows and displaying... i need to do some thing in where condn... but i cant use max() in where condition. how to do it to display only the second row based on greatest startdate.

 

thx in advance

shan

India.

 

 

Link to comment
Share on other sites

I can't test it now but you can give it a try

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] EmployeeName, ProjectName,StartDate,EndDate FROM emp_prj GROUP BY empid ASC, StartDate DESC [!--sql2--][/div][!--sql3--]

Link to comment
Share on other sites

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] EmployeeName, ProjectName, MAX(StartDate) AS MaxDate,EndDate FROM emp_prj GROUP BY empid ORDER BY empid ASC, MaxDate DESC [!--sql2--][/div][!--sql3--]

 

seems like this is still mixing

Link to comment
Share on other sites

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] EmployeeName, ProjectName, StartDate ,EndDate FROM emp_prj AS e1 WHERE StartDate=([span style=\'color:blue;font-weight:bold\']SELECT[/span] MAX(StartDate) FROM emp_prj e2 WHERE e2.empid=e1.empid ) GROUP BY empid [!--sql2--][/div][!--sql3--]

Link to comment
Share on other sites

[!--quoteo(post=329992:date=Dec 23 2005, 12:34 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 23 2005, 12:34 PM) 329992[/snapback][/div][div class=\'quotemain\'][!--quotec--]

what version of mysql are you using? it probably doesn't support subquery yet...

 

That sounds like the cause of the "syntax error" -- you'll have to use a temporary table to get around this in pre-MySQL 4.1.

Link to comment
Share on other sites

hi,

Ths for reply,

The version which i am using is..

mysql-3.23.58-1

If i hard code the date in the where condition,it wrks fine.but if i write the inner select query in the where condition it says:

Your SQL-query has been executed successfully

but the output is not shown..

 

Thks in advance,

Shan.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.