CODEWITHSUNDEEP
sql
user8259301
user8259301

Reputation:

unique records by one column

I have been working on this for a long time now and cant get the answer. I have a table with 4 columns. I need to return two of these columns, A and B. However I need the data to be filtered by the first column A. A is not a primary key. How can I achieve this? If I can return all the columns its also ok, as long as the data is only filtered by column A. 

A  B  C  D
1  e  f  r
1  e  f  r
1  e  k  t
2  c  f  r
2  c  f  r
3  l  f  r

This should return

A  B  C  D
1  e  f  r
2  c  f  r
3  l  f  r

Which query wil give me this result? Until now I have this but it does not work well:

SELECT DISTINCT A, B, FROM myTable WHERE C=f

Upvotes: 1

Views: 80

Answers (3)

Shiv
Shiv

Reputation: 77

use this 

with cte as 
(
select * , row_Number() over (Partition by A order by A) as aa from myTable
)
select * from cte where aa = 1

Upvotes: 1

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

You could use Row_Number() to rank the rows within each distinct A value, then get the first row of each.

SELECT
    a, b, c, d
FROM
    (SELECT
        a, b, c, d,
        Row_Number() OVER (PARTITION BY a ORDER BY b, c, d) rn
    FROM
        myTable) mt
WHERE
    rn = 1

Upvotes: 1

Amrith M
Amrith M

Reputation: 128

Try 

SELECT A,B FROM myTable WHERE C='f' GROUP BY A,B

Upvotes: 1

Related Questions