CODEWITHSUNDEEP
mysqlsql
Dark Knight
Dark Knight

Reputation: 503

SQL Query to find highest rated

SQL Query:

Aim:

Write a SQL query to retrieve the address of highest rated Phills Coffee in United States addressing format.

id   name         house   street          city          state  zip country rating
1   Best Buy        34    Main St        Carson           CA  98064 USA      9
2   Phills Coffee  4568   Sepulveda Blvd Torrance         CA  50833 USA      6
3   Starbucks        3    Ocean Blvd     Long Beach       WA  45093 USA      9
4   Phills Coffee   214   Carson St      Huntington Beach PA  89435 USA      4

US Addressing Format (For people outside USA):

http://bitboost.com/ref/international-address-formats/united_states/

My attempt:

SELECT house, street, city,
       state,country,zip 
  FROM table 
 WHERE name="Phills Coffee" 
 ORDER BY rating DESC LIMIT 1

Am I doing wrong? Or How can I improve this query?

Thanks,

Upvotes: 1

Views: 2213

Answers (4)

Rick Matthews
Rick Matthews

Reputation: 26

US address format would be like: 4568 Sepulveda Blvd, Torrance, CA 50833 USA.

So your select would look like: SELECT CONCAT(house, ' ', street, ', ', city, ', ', state, ' ', zip, ' ', country) FROM table WHERE name="Phills Coffee" ORDER BY rating DESC LIMIT 1

Upvotes: 1

J-Dizzle
J-Dizzle

Reputation: 3191

SELECT house, street, city, state, country, zip, rating
FROM table WHERE rating = (SELECT MAX(rating) 
    from table WHERE name = "Phills Coffee") 
AND name= "Phills Coffee";

This should return:

4568 Sepulveda Blvd Torrance CA 50833 USA 6

Note that you can omit rating from the first line of this query and it will return the address only (no 6, but will still select the max rating's info)

Upvotes: 1

user3758070
user3758070

Reputation: 26

You will have to use Max(rating) As Highest Rating and Group By for this,

SELECT house, street, city,
       state,country,zip,Max(rating)
FROM   table
Group  By house, street, city,
       state,country,zip 
Having name="Phills Coffee"  
ORDER  BY rating DESC LIMIT 1;

I hope it works out for you, I am sorry if I wasn't any help..

Upvotes: 1

Tarik Zouine
Tarik Zouine

Reputation: 146

You've been asked to retrieve the address, and in United States addressing format. So use that :

SELECT CONCAT(street,' ','country', ',') ...

Dont know what is the US addressing format, but use concat to get it done. Your WHERE condition and ORDER BY are OK

Upvotes: 1

Related Questions