I'm working on simple queries but can't figure out how to modify to produce the below output.
Number Name Flag
1 ABC NULL
1 DEF FG
1 DEF NULL
I need to produce this output:
Number Name Flag
1 ABC NULL
1 DEF FG
The logic is when Number and Name are same, take the rows with the Flag.
Simplest way, but I don't know if that meets your requirements if there is more than one non-NULL value.
SELECT Number, Name, Flag = MAX(Flag)
FROM dbo.Table
GROUP BY Number, Name;
Maybe something like this:
First some test data:
DECLARE @tbl TABLE(Number INT,Name VARCHAR(10),Flag VARCHAR(3))
INSERT INTO @tbl
VALUES
(1,'ABC',NULL),
(1,'DEF','FG'),
(1,'DEF',NULL)
The the query like this:
;WITH CTE AS
(
SELECT
RANK() OVER(PARTITION BY Name ORDER BY Flag DESC) AS iRank,
tbl.Number,
tbl.Name,
tbl.Flag
FROM
@tbl AS tbl
)
SELECT
*
FROM
CTE
WHERE
CTE.iRank=1
1 DEF GH?