Jump to content

Archived

This topic is now archived and is closed to further replies.

shan_cool

Filtering the result of a query.....

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.

 

 

Share this post


Link to post
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--]

Share this post


Link to post
Share on other sites

Hi Ryan,

thx for ur reply.. i tried that but still displays two records...

 

rgds,

shan.....

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Hi,

I tried that. but now it shows single record but with mixing of two rows.... date is correct but project name comes frm 1st row instead of 2nd row.. cartesian......

 

 

 

thx

shan

Share this post


Link to post
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--]

Share this post


Link to post
Share on other sites

Hi Ryan,

I tried it.. but it throws error message as

 

 

You have an error in your SQL syntax near 'SELECT MAX(StartDate) FROM emp_prj e2 WHERE e2.empid=e1.empid) GROUP' at line 1

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Yup, that's the problem -- you're using an older version of MySQL; you'll have to retrieve all of the max. dates first (either in middleware or via a temporary table), and then do the "outer" query to retrieve your result set.

Share this post


Link to post
Share on other sites

×

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.