Select top average from a table where the name of the object is in another table-CodePudding

I have to get the average top rating of a beer. I need to get this information from two tables, one table containing the rating and the other containing the name of the beer as follows;

The RB table contains people's assessments of beers: Systembolaget's article number (SYSNR), the person's name (NAME), and the rating (RATING).

The other table is; Table B contains information on different types of beer: Systembolaget's article number (SYSNR), producer (BREWERY), name of the beer (BEER), country of origin (COUNTRY) and liter price (PRICE).

With the following code I can get the top average rated beer;

select sysnr, avg(rating) as avg_rating from rb group by sysnr order by avg(rating) desc limit 1;

I try to combine it with the following code to get the name of the beer but I get this error: Error: sub-select returns 2 columns - expected 1

 select beer from b where sysnr in(select sysnr, avg(rating) as
 avg_rating 
 from rb 
 group by sysnr 
 order by avg(rating) desc limit 1);

CodePudding user response：

I think you were close. The pre-query to get the averages was ok. Once you had that limit 1, then join to the beer table to get the name of the beer.

select
      b.nsme,
      Avgs.avg_rating
   from
      ( select 
              sysnr, 
              avg(rating) avg_rating 
           from 
              rb 
           group by 
              sysnr 
           order by 
              avg(rating) desc 
           limit 1 ) Avgs
         JOIN b
            on Avgs.sysnr = b.sysnr

CodePudding user response：

I'm thinking to use RANK() here:

WITH cte AS (
    SELECT sysnr, AVG(rating) AS avg_rating, RANK() OVER (ORDER BY AVG(rating) DESC) rnk
    FROM rb
    GROUP BY sysnr
)

SELECT sysnr, avg_rating
FROM cte
WHERE rnk = 1;

CodePudding user response：

select beer, extra_data.avg_rating from b join (select sysnr as sysnr, avg(rating) as avg_rating from rb group by sysnr order by avg(rating)) extra_data on extra_data.sysnr = b.sysnr ;