CODEWITHSUNDEEP
sql-serversql-server-2005
truthseeker
truthseeker

Reputation: 2265

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 3rd 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.

Upvotes: 0

Views: 1831

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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

Upvotes: 3

Cameron Jordan
Cameron Jordan

Reputation: 759

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.

Upvotes: 0

Related Questions