CODEWITHSUNDEEP
sqlsql-server-2008
user1141584
user1141584

Reputation: 619

Conditional result from query based on column

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.

Upvotes: 2

Views: 178

Answers (2)

Arion
Arion

Reputation: 31239

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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;

Upvotes: 2

Related Questions