How to filter rows by values of one column?

I need to get several columns form sql query. Then I have to filter this answer by the "distinct" values of one column, but in the output I need to have all columns, not only this which values has to be distinct. Can anybody help me? Order by clause is not an answer for me.

A,B,C,D
E,F,G,H
I,J,C,L
M,N,Z,H

Above is a simple rows output. Please have a look onto 开发者_StackOverflow3rd column. Let's assume that we don't know how many rows do we have. I need to select only rows which has distinct value in 3rd column. (C,G,Z) - We need to filter anyone from "C" rows.

I've arbitrarily chosen to use col1 to break ties on col3. You can adjust the order by portion of the partition to suit your needs.

/* Set up test data */
declare @test table (
    col1 char(1),
    col2 char(1),
    col3 char(1),
    col4 char(1)
)

insert into @test
    (col1, col2, col3, col4)
    select 'A','B','C','D' union all
    select 'E','F','G','H' union all
    select 'I','J','C','L' union all
    select 'M','N','Z','H' 

/* Here's the query */
;with cteRowNumber as (
    select col1, col2, col3, col4, 
           row_number() over (partition by col3 order by col1) as RowNumber
        from @test
)
select col1, col2, col3, col4
    from cteRowNumber
    where RowNumber = 1

Returns

col1    col2    col3    col4
----------------------------
A       B       C       D
E       F       G       H
M       N       Z       H

ROLL UP or CUBE could be helpful for your problem, since they can aggregate (i.e. subtotal) data based on the GROUP BY and still return the individual rows.