| Author |
Topic |
|
anita.86
Starting Member
21 Posts |
 Posted - 2015-05-01 : 04:56:04
|
There is 2 tables
1st table student_reg (col- id, stu_name) ,
2nd table student_details (col-date_of_birth and interview_date,stu_id)
student_reg data.
1 Anna
2 smita
3 smita
4 madhuri
5 madhuri
6 madhuri
7 devi
student_details data.
date of birth interview_date student_id
1-3-2012 1-3-2012 1
1-1-2012 21-3-2012 2
1-2-2012 21-3-2012 3
31-1-2011 1-6-2012 4
31-1-2011 11-7-2012 5
31-1-2011 21-3-2012 6
31-1-2015 1-3-2012 7
my question is student-id 4,5,7 is same name and same dob birth
so last_applied_date will be of previous id interview_date
need output is :
student_id student_name dob_of_birth last_applied_date
4 Madhuri 31-1-2011 Null
5 Madhuri 31-1-2011 1-6-2012
6 Madhuri 31-1-2011 11-7-2012
Means
N/A |
|
|
anita.86
Starting Member
21 Posts |
Posted - 2015-05-01 : 07:56:55
|
can anyone tell the answer??plzzzzzzzzzzzz
N/A |
 |
|
|
anita.86
Starting Member
21 Posts |
Posted - 2015-05-01 : 07:56:56
|
can anyone tell the answer??plzzzzzzzzzzzz
N/A |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-05-01 : 08:22:38
|
This is clearly homework. We're happy to help but you need to have a go at it yourself first. Please try to write a query to do what you need, post it here, and we'll give you hints on next steps.
The thing is, if we answer homework questions, students will learn less. We want you to succeed, not just in this course, but in your subsequent professional career. |
|
|
|
anita.86
Starting Member
21 Posts |
Posted - 2015-05-01 : 11:56:17
|
select t.*,
CASE WHEN count(t.candidate_name)>1 and count(t.date_of_birth)>1 THEN min(c.interview_date)
end as last_appiled_date,
CASE WHEN cand_status= 0 THEN concat('Rejected in', " " , t.round)
WHEN cand_status= 1 THEN concat('Selected in', " " , t.round)
WHEN cand_status=2 THEN concat('On hold in', " " , t.round) end as Interview_status
from candidate_registration intr inner join candidate_personal_detail c on intr.id=c.candidate_id
left JOIN
(
select c.candidate_id,candidate_name,date_of_birth,c.interview_date,round,
(select can_status from candidate_status where id=max(b.id)) as cand_status
from candidate_status b right join candidate_registration a on a.id=b.candidate_id
inner join candidate_personal_detail c on a.id=c.candidate_id
left join interview_round i on i.id=b.round_id
group by a.id,candidate_name, date_of_birth
) t
on c.candidate_id = t.candidate_id
and c.interview_date = t.interview_date
group by intr.id,candidate_name, date_of_birth;
N/A |
|
|
|
anita.86
Starting Member
21 Posts |
Posted - 2015-05-01 : 11:57:24
|
i tried my best, but not getting perfect answer.
Thanks in advance..
N/A |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-05-01 : 12:23:54
|
This query should do it:
SELECT id
,stu_name
,date_of_birth
,lag(interview_date) OVER (
PARTITION BY stu_name ORDER BY interview_date
) AS last_applied_date
FROM student_reg r
INNER JOIN student_details d ON r.id = d.stu_id
ORDER BY stu_name
,id
,last_applied_date
Note that the results differ from what you expected. Analyzing that, you can see that for madhuri, Interview date of 1 June 2012, the previous date is 21 March 2012, not null. Same idea for the other rows. |
|
|
|
anita.86
Starting Member
21 Posts |
Posted - 2015-05-01 : 12:55:45
|
Showing error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( PARTITION BY candidate_name ORDER BY interview_date ) AS last_applied_date' at line 4 1.279 sec
N/A |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-05-01 : 13:04:33
|
quote:
Originally posted by anita.86
Showing error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( PARTITION BY candidate_name ORDER BY interview_date ) AS last_applied_date' at line 4 1.279 sec
You are using MySQL. SQLTeam.com is for Microsoft SQL Server. You'll want to post your question on a site that specializes in MySQL.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Need urjent output