CODEWITHSUNDEEP
mysqlsqldatabasegreatest-n-per-group
screamalltheway
screamalltheway

Reputation: 13

Sql Statement: How do I get the highest rating and the product for each location?

How do I get the highest rating and the product for each location? This is more to sql statement. Can anyone please help to spot where is the error? It doesn't retrieve the values I wanted.

*Using Mysql database

This is my sql statement:

SELECT r.rating,
       r.product,
       l.places,
       l.address,
       l.telephone,
       l.lng,
       l.lat
FROM   locations AS l
       LEFT JOIN ratings AS r
         ON l.places = r.places
ORDER  BY r.rating DESC

Upvotes: 1

Views: 1043

Answers (3)

John Woo
John Woo

Reputation: 263803

Since, you haven't mentioned the database server you are using, the query below will work on almost all RDBMS including MySQL 

SELECT  r.rating, 
        r.product, 
        l.places, 
        l.address, 
        l.telephone, 
        l.lng, 
        l.lat 
FROM    locations AS l 
        INNER JOIN ratings AS r 
            ON l.places = r.places 
        (
            SELECT  a.places, b.product, MAX(b.rating) max_rating
            FROM    locations a
                    INNER JOIN ratings b
                        ON a.places = b.places 
            GROUP   BY a.places, b.product
        ) c ON l.places = c.places AND
                r.product = c.product AND
                r.rating = c.max_Rating
ORDER   BY r.rating desc

OR if your RDBMS supports window functions,

SELECT  rating, product,  places, 
        address,  telephone,  lng,  lat
FROM
    (
        SELECT  r.rating, r.product,  l.places, 
                l.address,  l.telephone,  l.lng,  l.lat 
                ROW_NUMBER() OVER (PARTITION BY r.product,  l.places
                                    ORDER BY r.rating DESC) rn
        FROM    locations AS l 
                INNER JOIN ratings AS r 
                    ON l.places = r.places 
    )   x
WHERE   x.rn = 1    
ORDER   BY rating desc

Upvotes: 1

whytheq
whytheq

Reputation: 35577

assuming later version of sql-server. This is not tested - I'm a little unsure about the correct partition ...either l.places or r.product. Try this...

;WITH cte
    as
    (
    SELECT     
               r.rating,
           r.product,
           l.places,
           l.address,
           l.telephone,
           l.lng,
           l.lat,
           [rnk] = RANK OVER(PARTITION BY l.places ORDER BY r.rating DESC)
    FROM   locations AS l
           LEFT JOIN ratings AS r
             ON l.places = r.places
    )
SELECT
        rating,
        product,
        places,
        address,
        telephone,
        lng,
        lat
FROM cte
WHERE [rnk] = 1
ORDER  BY rating DESC

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

SQL-Server version (2005 and greater):

WITH x 
     AS (SELECT r.rating, 
                r.product, 
                l.places, 
                l.address, 
                l.telephone, 
                l.lng, 
                l.lat, 
                RN = Rank() 
                       OVER( 
                         partition BY l.places 
                         ORDER BY rating DESC) 
         FROM   locations AS l 
                INNER JOIN ratings AS r 
                        ON l.places = r.places 
SELECT *  FROM   x  WHERE  rn = 1

Upvotes: 1

Related Questions