Unique vs distinct multi-column in Oracle 9i

Question

Hi,

I am a bit confused about the uses of these words. I have a table with he following columns: SITE, LAT, LONG, NAME, ......

I want results with unique (or is it distinct) LAT, LONG. How do I achieve this?

Answer 1

AFAIR both mean the same. To get unique vel distinct LAT & LONG from your table just do:

SELECT DISTINCT LAT, LONG FROM table;

Answer 2

UNIQUE is used for defining contraints on the data that can be stored in the table.

DISTINCT is used in queries to remove duplicates from the result set, without affecting the underlying table data.

Answer 3

select unique colA, colB from atable

select distinct colA, colB from atable

In this context, unique and distinct mean the same thing.

Distinct however is ANSI standard, whereas unique is not.

Please note that unique has many other meanings when used in other area's ie index creation etc.

Answer 4

I always like to see a count of how many of each unique item there is so I would:

select lat,long, count(lat) from table group by lat,long but thats just me