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(+)
Coco BeansDesignerAsked:
Who is Participating?
 
Coco BeansDesignerAuthor Commented:
Because of the left outer join, none of the above solutions worked.  I wrote a insert and update statement that worked best.
1
 
Pawan KumarDatabase ExpertCommented:
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(+)
0
 
Pawan KumarDatabase ExpertCommented:
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

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Coco BeansDesignerAuthor Commented:
You can't use registered = 'Y' AND DATE = MAX DATE ONLY.  This was only pseudo code as an example.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
or we can use ranking functions also..

SELECT r.student_id
		from 
		(
			select student_id , ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY date desc) rnk
			from registrations
			where registered = 'Y' 
		)r INNER JOIN students st ON r.student_id = st.Student_id
		WHERE rnk = 1

Open in new window

0
 
awking00Commented:
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;
0
 
Pawan KumarDatabase ExpertCommented:
@awking00-
Almost similar to what i have posted :) same thinking... :)
0
 
awking00Commented:
I'm not sure you really want the left join since it will return all student_ids from students regardless of whether or not they exist in the registrations table. Perhaps the following would be more meaningful
select s.student_id from
students s,
(select student_id,
 row_number() over (partition by student_id order by date desc) rn
 from registrations
 where registered - 'Y') r
where s.student_id = r.student_id
and r.rn = 1;
0
 
Pawan KumarDatabase ExpertCommented:
@awking00
-Your last post is same as my 2nd last post.
0
 
awking00Commented:
Sorry, I didn't see your post until after I submitted.
0
 
Coco BeansDesignerAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
Have you tried my last suggestions?

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

or

SELECT r.student_id
            from
            (
                  select student_id , ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY date desc) rnk
                  from registrations
                  where registered = 'Y'
            )r INNER JOIN students st ON r.student_id = st.Student_id
            WHERE rnk = 1
0
 
Coco BeansDesignerAuthor Commented:
Thanks
0
 
Pawan KumarDatabase ExpertCommented:
I also posted the same suggestion before the selected but my comment was not selected. Thanks
0
 
Coco BeansDesignerAuthor Commented:
Just an accident. Not on purpose.

Thanks for both your help.
0
 
Pawan KumarDatabase ExpertCommented:
You can ask the mods to open the question for you and then you allocate the points again.
0
 
Pawan KumarDatabase ExpertCommented:
Okies. I that case we can go ahead and delete the question.
0
 
Coco BeansDesignerAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.