CODEWITHSUNDEEP
sql-serversql-server-2008-r2uniquedistinct
user3121776
user3121776

Reputation: 3

Filter out one column value depending on other column value

Been stuck on a problem for a while. I give you and example below.

I have this table 

ID       | Dip  | Azi   |
SV12360  |-45   | 229.3 |
SV12360  |-45.1 | 228.3 |
SV12360  |-44.8 | 223.3 |
SV12359  |-39   | 249.3 |
SV12359  |-39.4 | 279.3 |
SV12357  |-55   | 290.3 |
SV12357  |-57   | 210.3 |
SV12318  |-47   | 212.3 |
SV12318  |-47.2 | 241.3 |
SV12317  |-41   | 289.3 |
SV12317  |-40   | 211.3 |
------------------------- etc.

I would like to create a query that give me only the first value for every ID. For example in this case I would like to see a distinct ID with first Dip and Azi value.

ID        Dip   Azi
SV12360 | -45 | 229.3
SV12359 | -39 | 249.3
SV12357 | -55 | 290.3

And etc. The tables contains about 19000 rows with and I don't want to manually copy-paste the values for every ID.

Upvotes: 0

Views: 100

Answers (2)

sumit
sumit

Reputation: 15464

try below



WITH ABC AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY ID)'rank' FROM TBL)

    SELECT * FROM ABC 
    WHERE rank= 1

OR

select * from(
(SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)'rank'
             FROM TBL)
             )tmp where rank=1

Upvotes: 0

slavoo
slavoo

Reputation: 6086

SELECT  t2.*
FROM    (
        SELECT  DISTINCT ID
        FROM    <yourTable>
        ) t1
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    <yourTable> t
        WHERE   t.ID = t1.id
        --ORDER BY <column> --you can specify which record is first            
        ) t2

Upvotes: 1

Related Questions