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,
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `table` ADD INDEX `table_idx_name_rating` (`name`,`rating`);
ALTER TABLE `table` ADD INDEX `table_idx_rating` (`rating`);
SELECT
table.house,
table.street,
table.city,
table.state,
table.country,
table.zip
FROM
table
WHERE
table.name = 'Phills Coffee'
ORDER BY
table.rating DESC LIMIT 1