Re: simple query

esukkar_at_gmail.com wrote:

> select site from site_table
> and the result would be:
>
> washington
> washington
> washington
> washington dc
> washington
> washington
> wash. dc
> wasingtonDc
>
> here there are 8 results - or rows. However, if i used select
distinct,
> i would still get 4 rows, as there are 4 distinct entries. However, I
> only want 1 result. I dont care which one it returns, just as long it
> only ever returns one result.

>From a data perspective, all these distinct values are different
values. Sure, you may apply common sense and know that these distinct names refer to the same place name, but the computer (working with 0's and 1's) does not.

You could use the SOUNDEX function - however it will fail to abbreviations into consideration as it deals with words and sounds.

You could use MAX or MIN and blindly select the first or last value.

Or my choice - you could do a proper relational database design, in 3rd normal form, and do away with data redundancy issues like this.

Relational theory is not something that is simply a nice-to-have. The vast majority of bugs and problems and performance and nasty issues in real world database systems are simply because relational theory has been ignored.

--
Billy