Re: nested sql

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
>
> Maxim

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