shan_cool Posted December 23, 2005 Share Posted December 23, 2005 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. Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/ Share on other sites More sharing options...
ryanlwh Posted December 23, 2005 Share Posted December 23, 2005 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--] Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10323 Share on other sites More sharing options...
shan_cool Posted December 23, 2005 Author Share Posted December 23, 2005 Hi Ryan, thx for ur reply.. i tried that but still displays two records... rgds, shan..... Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10324 Share on other sites More sharing options...
ryanlwh Posted December 23, 2005 Share Posted December 23, 2005 [!--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 Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10325 Share on other sites More sharing options...
shan_cool Posted December 23, 2005 Author Share Posted December 23, 2005 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 Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10326 Share on other sites More sharing options...
ryanlwh Posted December 23, 2005 Share Posted December 23, 2005 [!--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--] Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10327 Share on other sites More sharing options...
shan_cool Posted December 23, 2005 Author Share Posted December 23, 2005 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 Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10328 Share on other sites More sharing options...
ryanlwh Posted December 23, 2005 Share Posted December 23, 2005 what version of mysql are you using? it probably doesn't support subquery yet... Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10331 Share on other sites More sharing options...
fenway Posted December 23, 2005 Share Posted December 23, 2005 [!--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. Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10334 Share on other sites More sharing options...
shan_cool Posted December 26, 2005 Author Share Posted December 26, 2005 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. Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10405 Share on other sites More sharing options...
fenway Posted December 26, 2005 Share Posted December 26, 2005 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. Quote Link to comment https://forums.phpfreaks.com/topic/3088-filtering-the-result-of-a-query/#findComment-10413 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.