Select DISTINCT, return entire row

In SQL Server 2005 and above:

;WITH    q AS        (        SELECT  *, ROW_NUMBER() OVER (PARTITION BY col6 ORDER BY id) rn        FROM    mytable        )SELECT  *FROM    qWHERE   rn = 1

In SQL Server 2000, provided that you have a primary key column:

SELECT  mt.*FROM    (        SELECT  DISTINCT col6        FROM    mytable        ) mtoJOIN    mytable mtON      mt.id =         (        SELECT  TOP 1 id        FROM    mytable mti        WHERE   mti.col6 = mto.col6        -- ORDER BY        --      id        --  Uncomment the lines above if the order matters        )

Update:

Check your database version and compatibility level:

SELECT  @@VERSIONSELECT  COMPATIBILITY_LEVELFROM    sys.databasesWHERE   name = DB_NAME()

The key word "DISTINCT" in SQL has the meaning of "unique value". When applied to a column in a query it will return as many rows from the result set as there are unique, different values for that column. As a consequence it creates a grouped result set, and values of other columns are random unless defined by other functions (such as max, min, average, etc.)

If you meant to say you want to return all rows for which Col006 has a specific value, then use the "where Col006 = value" clause.

If you meant to say you want to return all rows for which Col006 is different from all other values of Col006, then you still need to specify what that value is => see above.

If you want to say that the value of Col006 can only be evaluated once all rows have been retrieved, then use the "having Col006 = value" clause. This has the same effect as the "where" clause, but "where" gets applied when rows are retrieved from the raw tables, whereas "having" is applied once all other calculations have been made (i.e. aggregation functions have been run etc.) and just before the result set is returned to the user.

UPDATE:

After having seen your edit, I have to point out that if you use any of the other suggestions, you will end up with random values in all other 9 columns for the row that contains the value "item1" in Col006, due to the constraint further up in my post.

select * from yourTable where column6 in (select distinct column6 from yourTable);