Select unique lines from result table

Question

I have a self join result table which I have a lot of duplicated lines (because I have history on that table as well..).
the result table is (date, date, varchar, int, int, int, int).
how can I return only unique lines (where all of the columns identical..)

Thanks!

Answer 1

It depends what you mean by unique; given a table:

create table example
(
    col1 int
    ,col2 int
)
insert example (col1,col2) values (1,2)
insert example (col1,col2) values (1,2)
insert example (col1,col2) values (1,3)
insert example (col1,col2) values (1,4)

Would you want the result to be:

1,2
1,3
1,4

(i.e. the returned rows are unique; though there may be multiple rows with these same values in the source table)

or:

1,3
1,4

(i.e. you only want those rows which were unique in the source data to begin with)

If the former, use:

select distinct col1
, col2 
from example

If the latter, use:

select col1
, col2 
from example
group by col1
, col2
having count(1) = 1

SQL Fiddle: http://sqlfiddle.com/#!3/594ac/1

Answer 2

You can use DISTINCT

select distinct * 
from yourTable