CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
KentZhou
KentZhou

Reputation: 25553

How to write sql for this case?

Suppose I have a table(a relationship) like

MyTab(ID1,ID2,IsMarked, data,....)

the sample data maybe looks like:

1,  1, 1, ...
1,  2, 0, ...
1,  3, 0, ...

2,  34, 1, ...

3,  4, 0, ...

4,  546, 0, ...
4,  8, 0, ...

Only one could be marked for each ID1. I want to get data marked as 1 for all Entities ID1. If there is no marked record, get the first one or any one of them.

For above sample data, the result should be:

1,  1, 1, ...

2,  34, 1, ...

3,  4, 0, ...

4,  546, 0, ...

Union could be a solution, but is too long and may have bad performance. My idea is to sort the data by ID1 and IsMarked desc, the get the first 1 for each ID1, but how to write a SQL for this case?

Upvotes: 0

Views: 87

Answers (2)

i-one
i-one

Reputation: 5120

For Only one could be marked for each ID1 the following should work:

;with cte as (
    select *, rn=row_number() over (partition by ID1 order by IsMarked desc)
)
select *
from cte
where rn=1

Upvotes: 3

user1161625
user1161625

Reputation: 662

Shot in the dark:

SELECT A.*
FROM MYTAB A
    LEFT JOIN (
        SELECT MAX(ID2) AS MAXID2, ID1
            FROM MYTAB
                WHERE ISMARKED=1
                    GROUP BY ID1
    ) B ON A.ID2=B.MAXID2 AND A.ID1=B.ID1
        LEFT JOIN (
            SELECT MAX(ID2) AS MAXID2, ID1
                FROM MYTAB
                    WHERE ISMARKED=0
                        GROUP BY ID1
        ) C ON A.ID2=C.MAXID2 AND A.ID1=C.ID1
            WHERE
                (B.ID1 IS NOT NULL)
                OR
                (B.ID1 IS NULL AND C.ID1 IS NOT NULL);

Upvotes: 0

Related Questions