Re: nested sql
Date: Sat, 9 Aug 2008 11:53:10 -0700 (PDT)
On Aug 9, 11:03 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Shirley Cohen schrieb:
> > Hi,
> > I need help with a sql statement. Suppose I have a table
> > movies(movie_title, movie_rating, date_rated, user) and I want to find
> > the highest ranked movies for each user. Rather than write several sql
> > statements, I'd like perform this query in a single nested sql
> > statement. Does anyone know how to do that?
> > Thanks,
> > Shirley
> Probably you can do it utilizing analytic functionshttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
> If you need more detailed advice, you need to provide more detailed
> info, like your oracle version, sample data, sample of desired output,
> what the logic should be if some movies are equally high rated for
> certain users etc.
> And it would be nice to see, what you have tried sofar.
> Best regards
Have you considered:
SELECT A.movie_title, A.movie_rating, A.date_rated, A.rater_name
from movie_ratings A
where A.movie_rating = ( select max( movie_rating )
from movie_ratings where rater_name = A.rater_name ) ;
Of course, some movie raters will score more than one movie with the same value, thus a single movie rater may appear multiple times in the output. Received on Sat Aug 09 2008 - 13:53:10 CDT