Link to home
Start Free TrialLog in
Avatar of Coco Beans
Coco Beans

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(+)
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Please use like below-

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(+)
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(+)

Open in new window

Avatar of Coco Beans
Coco Beans

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

Open in new window

SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of awking00
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;
@awking00-
Almost similar to what i have posted :) same thinking... :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@awking00
-Your last post is same as my 2nd last post.
Sorry, I didn't see your post until after I submitted.
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
I also posted the same suggestion before the selected but my comment was not selected. Thanks
Just an accident. Not on purpose.

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Okies. I that case we can go ahead and delete the question.
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