CODEWITHSUNDEEP
sqlsql-server
usr5860
usr5860

Reputation: 13

sql column based on condition in same column

below is the table

id      projectid       statusid
100     2971            1
101     4637            1
102     4637            2
103     6144            2
104     6510            1

basically i want all the id, projectid whose status is 1, if 1 is not available then id of whose statusid of 2 like below result. if we have both the status then only 1 should come

id      projectid       statusid
100     2971            1
101     4637            1
103     6144            2
104     6510            1

i tried union but i took 3 temp tables to achieve it looking for some better options.

Upvotes: 0

Views: 37

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Use Row_Number

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY statusid ASC),
        Id,
        ProjectId,
        statusid
        FROM YourTable
)
SELECT
    Id,
        ProjectId,
        statusid
        FROM CTE    
            WHERE RN = 1

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one trick using Row_Number and Top 1 with ties

select top 1 with ties * 
from yourtable 
order by row_number()over(partition by projectid order by statusid asc)

Upvotes: 1

Related Questions