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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.