CODEWITHSUNDEEP
sqlsql-server
noname
noname

Reputation: 565

Unique query give me duplicate rows

I have a database in SQL Server and one table which I have to use to display unique values base on column_one without using distinct so I came up with solution:

select p.id, p.one, two, w.five, p.eight
from table_one p with (nolock)
join table_two w with (nolock) on w.one = p.one
where
w.eight between convert(date, '10/05/2020', 103) and dateadd(d, 7, convert(date, '10/05/2020', 103)) and
p.twelve = 2 
and p.id in (SELECT max(id) FROM table_one a with(nolock) GROUP BY two)
order by p.id desc

and I should get two rows, but I have 3, second row is duplicated? Why is that? I was trying to googling some examples and I found my solution as one of them. So what is wrong with it? Any suggestion will be helpful.

PS. I can confirm that subquery select max(id)... give me unique values.

EDITED Sorry for missing that example earlier. I hope is more clear now, what I want achieve.

table_one 
id | one      |  two | eight| twelve
-------------------------------------
1  |  value_1 | r1c2 | r1c8 | 2
2  |  value_1 | r2c2 | r2c8 | 2
3  |  value_2 | r3c2 | r3c8 | 2
4  |  value_2 | r4c2 | r4c8 | 2

table_two 
id | one      | five | eight
---------------------------------
1  |  value_1 | r1c5 | 22/03/2020
2  |  value_1 | r2c5 | 24/03/2020
3  |  value_2 | r3c5 | 24/03/2020
4  |  value_2 | r4c5 | 25/04/2020

result expected:
id | one     | two    | eight
-----------------------------------
2  | value_1 |  r2c2  | 24/03/2020
4  | value_2 |  r4c2  | 25/04/2020

I think I figured it out, but please correct me if I am wrong, is that because I am JOINing table on column one which is not unique?

Upvotes: 0

Views: 117

Answers (2)

Farshid Shekari
Farshid Shekari

Reputation: 2449

That's true because when you join two tables on one column and it has duplicates values in that field, you get duplicate rows in your results. for your task, you can use window functions like this:

SELECT *
FROM (
    select 
        p.*,ROW_NUMBER() OVER (PARTITION BY w.one ORDER BY w.eight DESC) AS rn
    from table_one p
    join table_two w on w.one= p.one
) t
WHERE t.rn = 1
ORDER by t.id asc

Upvotes: 0

Zhorov
Zhorov

Reputation: 29933

It's difficult without sample data and expected output, but I think that the following approach using ROW_NUMBER() is a possible option. You need to use the correct columns in the PARTITION BY and ORDER BY clauses:

SELECT *
FROM (
    select 
        p.id, p.one, p.two, w.five, p.eight,
        ROW_NUMBER() OVER (PARTITION BY p.two ORDER BY p.id DESC) AS rn
    from table_one p with (nolock)
    join table_two w with (nolock) on w.one= p.one
    where 
        w.eight between convert(date, '10/05/2020', 103) and dateadd(d, 7, convert(date, '10/05/2020', 103)) and
        p.two = 2
) t
WHERE t.rn = 1
ORDER by t.id DESC

Upvotes: 1

Related Questions