Re: nested sql

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Sun, 10 Aug 2008 12:00:41 +0000 (UTC)
Message-ID: <g7ml99$sb1$1@registered.motzarella.org>

On Sat, 09 Aug 2008 10:36:49 -0700, Shirley Cohen wrote:

> 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

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

Received on Sun Aug 10 2008 - 07:00:41 CDT