I have a database table like the following.
id | value |flag
2 null 1
2 z 2
3 x 1
3 y 2
3 z 3
4 null 2
I need result like the following
id | value |flag
2 z 2
3 x 1
4 null 2
If the value is null, the value from other matched id should be taken (2-z-2). If all the rows of an id are not null then the value should be taken by priority which is 1 here (3-x-1). If an id has only null value, take any single row with null value and flag does not matter here.
This query will take the value of the first not null value (min flag value) or the null row otherwise.
You data:
DECLARE @table TABLE
( id INT , val CHAR(1) , flag INT );
INSERT INTO @table
( id, val, flag )
VALUES ( 2, NULL, 1 )
, ( 2, 'z', 2 )
, ( 3, 'x', 1 )
, ( 3, 'y', 2 )
, ( 3, 'z', 3 )
, ( 4, NULL, 2 ); -- NULL only value
Query:
SELECT id ,
val ,
flag
FROM ( SELECT * ,
n = ROW_NUMBER() OVER ( PARTITION BY id ORDER BY CASE
WHEN val IS NULL
THEN 0
ELSE 1
END DESC, flag )
FROM @table AS t
) AS o
WHERE n = 1;
ROW_NUMBER() partitions by id and for each id, it orders by 0 or 1 (null/not null) and flag. The case is used to order by not null values first (=1) and then by null values (=0).
Output:
id val flag
2 z 2
3 x 1
4 NULL 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments