nested sql

[Shirley Cohen]

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

[Maxim Demenko]

Shirley Cohen schrieb:

Probably you can do it utilizing analytic functions
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407
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

[louisjoh...@gmail.com]

On Aug 9, 11:03 am, Maxim Demenko <mdeme...@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.

[hpuxrac]

What version of oracle exactly?

Analytic functions can do this pretty easily. Even without many of
the analytics you can do this in 1 sql statement.

This sounds very much like a homework problem though. What sql have
you come up with so far?

[Mladen Gogala]

with survey as (
select movie_title,
user,
movie_rating,
max(movie_rating) over (partition by user) as max_rating
from movies)
select user,movie_title from survey where movie_rating = max_rating

--
http://mgogala.freehostia.com