SQL Group by using the First N elements in each gr

Question

This question already has an answer here:

• Top N per Group Sql problem in mysql 1 answer

Suppose I have the next table:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      1     |     2   |
|      1     |     4   |
|      1     |     5   |
|      2     |     3   |
|      2     |     4   |
|      2     |     2   |
|      3     |     1   |
|      3     |     2   |
|      3     |     3   |
|      3     |     5   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

I would like to get the average by group BUT using the first 2 elements on each group.
Example:

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

answer expected:

+------------+---------+
|    MovieId |  AVG    |
+------------+---------+
|      1     |     3.5 |
|      2     |     3.5 |
|      3     |     1.5 |
|      4     |     3   |
+------------+---------+

This is the SQL query I have to get the AVG for all of the movies. But as I said, I would like to use just the first 2 elements for each group.

SELECT movieid, AVG(cast(rating as DECIMAL(10,2))) AS AVG FROM ratings group by movieid

If you can help me to make the SQL I appreciate. I will also use Linq just in case some of you know it.

Answer 1

In a SQL DBMS -- as in the relational model -- there is no "first". Do you mean any arbitrary 2 rows for each movie, or the two highest ratings, or something else?

If you can't define an order, then the query is meaningless.

If you can define an order, join the table to itself as I show in my canonical example to create a ranking, and select where RANK < 3.

Answer 2

FOR Mysql:-

select id, avg(rating) 
from (SELECT a.*, @num := @num + 1 rownum,
        (select count(*) 
         from movies m 
         where m.id<=a.id) last_count,

        (select count(*) 
         from movies m1 
         where a.id=m1.id) grp_count 
      from movies a, (SELECT @num := 0) d) f  
where grp_count-(last_count-rownum)<=2 
group by id;

you can use rownum function in oracle. And row_number() function in sql server.

Answer 3

This is a solution in SQL

Create table #tempMovie (movieId int ,rating int)

INSERT INTO #tempMovie
Select  *  from table where movieidid=1 Limit 2
Union all
Select *  from table where movieidid=2 Limit 2
Union all
Select *  from table where movieidid=3 Limit 2
Union all
Select  *  from table where movieidid=4 Limit 2

Temporary table #tempmovie table will contain data like this

+------------+---------+
|    MovieId |  rating  |
+------------+---------+
|      1     |     4   |
|      1     |     3   |
|      2     |     3   |
|      2     |     4   |
|      3     |     1   |
|      3     |     2   |
|      4     |     4   |
|      4     |     2   |
+------------+---------+

then apply group by

Select movieId, AVG(rating)
from #tempMovie
Group by movieId

Drop table #tempmovie