Jump to content


Photo

Filtering the result of a query.....


  • Please log in to reply
10 replies to this topic

#1 shan_cool

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

Posted 23 December 2005 - 06:24 AM

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.



#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 23 December 2005 - 06:37 AM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 shan_cool

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

Posted 23 December 2005 - 06:46 AM

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

rgds,
shan.....

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 23 December 2005 - 06:58 AM

[!--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
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 shan_cool

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

Posted 23 December 2005 - 07:08 AM

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

#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 23 December 2005 - 07:15 AM

[!--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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 shan_cool

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

Posted 23 December 2005 - 07:30 AM

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


#8 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 23 December 2005 - 05:34 PM

what version of mysql are you using? it probably doesn't support subquery yet...
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 December 2005 - 09:01 PM

[!--quoteo(post=329992:date=Dec 23 2005, 12:34 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 23 2005, 12:34 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
what version of mysql are you using? it probably doesn't support subquery yet...
[/quote]

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 shan_cool

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

Posted 26 December 2005 - 06:00 AM

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.

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 December 2005 - 03:39 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users