Query for View with unique entries from Database with repeated entries

database-designoracle-11gperformancequery-performance

I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any suggestions on how to go about it?
Eg: There may be 100 hotels with city_name = Miami and country_name = USA. The view should however contain only 1 city_name = Miami and country_name = USA.

I figured I'd need a loop to run through the table but still struggling to put together a query.

Thanks in advance!

Best Answer

The simplest solution is this:

SELECT DISTINCT city_name, country_name FROM yourtable

That takes the list of result rows and returns each one only once, even if it appears in the table multiple times. See "DISTINCT" in http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm fro details.

If you also want to know mor information about each row group, like how many hotels exist in each city you can use GROUP BY instead:

SELECT city_name, country_name, COUNT(1) AS Cnt
  FROM yourtable
 GROUP BY city_name, country_name

This again returns each city only once but additionally counts how often it appears in the table. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2065777 for more details on that.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Washington','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','New York','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','New York','USA');

Query 1:

SELECT DISTINCT city_name,country_name FROM hotels

Results:

|  CITY_NAME | COUNTRY_NAME |
-----------------------------
|    Seattle |          USA |
| Washington |          USA |
|   New York |          USA |

Query 2:

SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 GROUP BY city_name,country_name

Results:

|  CITY_NAME | COUNTRY_NAME | CNT |
-----------------------------------
|    Seattle |          USA |   3 |
| Washington |          USA |   1 |
|   New York |          USA |   2 |

If you want to create a view for each country you can just add a WHERE clause with the country name like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Hamburg','Germany');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','Paris','France');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','Paris','France');


CREATE VIEW US_Hotels 
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'USA'
 GROUP BY city_name,country_name;


CREATE VIEW Ger_Hotels 
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'Germany'
 GROUP BY city_name,country_name;

Query 1:

SELECT * FROM US_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Seattle |          USA |   3 |

Query 2:

SELECT * FROM Ger_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Hamburg |      Germany |   1 |
Related Question