CODEWITHSUNDEEP
sqlsql-server
user3112991
user3112991

Reputation: 31

SELECT distinct lines

With this request :

SELECT * 
FROM 
   (SELECT DISTINCT 
       ROW_NUMBER() OVER (ORDER BY test2.A1 ASC) AS line, 
       test1.A1, test1.A2, test2.A1
    FROM 
       TB_TEST1 test1
JOIN 
       TB_TEST2 test2 ON test2.A3 != 'test' AND test2.A4 = test1.A4
    WHERE 
       test1.A3 != 'test') AS sub
WHERE 
    line BETWEEN 1 AND 50

I get : 

 1  a   1   AAA
 2  a   1   BBB
 3  b   7   AAA
 4  b   7   BBB
 5  c   11  AAA
 6  c   12  BBB

But I want only lines where second column and third column are unique : 

 1  c   11  AAA
 2  c   12  BBB

How can I do this ?

Upvotes: 0

Views: 218

Answers (1)

fnightangel
fnightangel

Reputation: 426

Try move rownumber to outside the subquery:

SELECT 
    ROW_NUMBER() OVER (ORDER BY sub.TEST1_A1 ASC) AS line
,   TEST1_A1
,   TEST1_A2
,   TEST2_A1
FROM (
    SELECT DISTINCT test1.A1 AS TEST1_A1, test1.A2 AS TEST1_A2, test2.A1 AS TEST2_A1
    FROM TB_TEST1 test1
    JOIN TB_TEST2 test2 ON test2.A3!='test' AND test2.A4 = test1.A4
    WHERE test1.A3!='test') as sub
WHERE line BETWEEN 1 AND 50

Upvotes: 1

Related Questions