Remove duplicate rows on a SQL query

Question

Possible Duplicate:
Duplicate result
Interview - Detect/remove duplicate entries

I have a SQL Query, which returns a table with one column.

The returned data may be duplicate. for example, my query may be something like:

SELECT item FROM myTable WHERE something = 3

and the returned data may be something like this:

item
-----
2
1
4
5
1
9
5

My Question is, How to remove duplicated items from my query?

I mean, I want to get these results:

item
-----
2
1
4
5
9

Please note that I don't want to change or delete any rows in table. I just want to remove duplicates in that query.

How to do that?

Solution 1

SELECT DISTINCT item FROM myTable WHERE something = 3

Solution 2

As noted, the distinct keyword eliminates duplicate rows—where the rows have identical values in column—from the result set.

However, for a non-trivial query against a properly designed database, the presence of duplicate rows in the result set — and their elimination via select distinct or select ... group by is, IMHO, most often a "code smell" indicating improper or incorrect join criteria, or a lack of understanding of the cardinalities present in relationships between tables.

If I'm reviewing the code, select distinct or gratuitous group by without any obvious need present will get the containing query flagged and that query gone over with a fine toothed comb.

Solution 3

You need to add the DISTINCT keyword to your query.
This keyword is pretty standard and supported on all major databases.

See DISTINCT refs here

Solution 4

SELECT DISTINCT item FROM myTable WHERE something = 3

You just have to use distinct

View more solutions