asked on
Return one row in a subquery
Hi,
I have a subquery but it's returning more than 1 row. I only want the latest date available.
See below AND DATE = MAX DATE ONLY)
can anyone help?
Thank you
select st.student_id
from (select student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY) reg,
students st
where st = reg.student_id(+)
I have a subquery but it's returning more than 1 row. I only want the latest date available.
See below AND DATE = MAX DATE ONLY)
can anyone help?
Thank you
select st.student_id
from (select student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY) reg,
students st
where st = reg.student_id(+)
SQL
Last Comment
Coco Beans
or this ..
select st.student_id
from (select student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY
GROUP BY student_id
) reg,
students st
where st = reg.student_id(+)
ASKER
You can't use registered = 'Y' AND DATE = MAX DATE ONLY. This was only pseudo code as an example.
Okies. Please try this -
select reg.student_id
from
(
SELECT student_id
from
(
select student_id , MAX(DATE) dates
from registrations
where registered = 'Y'
group by Student_id
)x INNER JOIN registrations r ON r.Student_ID = x.student_id AND x.dates = r.date
)reg INNER JOIN students st ON reg.student_id = st.Student_id
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Should work for SQL Server and Oracle -
select s.student_id from
students s
left join
(select student_id,
row_number() over (partition by student_id order by date desc) rn
from registrations
where registered - 'Y') r
on s.student_id = r.student_id
where r.rn = 1;
select s.student_id from
students s
left join
(select student_id,
row_number() over (partition by student_id order by date desc) rn
from registrations
where registered - 'Y') r
on s.student_id = r.student_id
where r.rn = 1;
@awking00-
Almost similar to what i have posted :) same thinking... :)
Almost similar to what i have posted :) same thinking... :)
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@awking00
-Your last post is same as my 2nd last post.
-Your last post is same as my 2nd last post.
Sorry, I didn't see your post until after I submitted.
ASKER
If you notice this is a left outer join...
select st.student_id
from (select student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY
GROUP BY student_id
) reg,
students st
where st = reg.student_id(+)
When I use your code it enforces an inner join
WHERE rnk = 1
select st.student_id
from (select student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY
GROUP BY student_id
) reg,
students st
where st = reg.student_id(+)
When I use your code it enforces an inner join
WHERE rnk = 1
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
I also posted the same suggestion before the selected but my comment was not selected. Thanks
ASKER
Just an accident. Not on purpose.
Thanks for both your help.
Thanks for both your help.
You can ask the mods to open the question for you and then you allocate the points again.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Okies. I that case we can go ahead and delete the question.
ASKER
Neither solution worked completely. Both enforced an inner join by joining to the subquery. I changed it to an insert and then update. This worked the best.
Thanks for all your help
Thanks for all your help
select st.student_id
from (select TOP 1 student_id
from registrations
where registered = 'Y' AND DATE = MAX DATE ONLY) reg,
students st
where st = reg.student_id(+)