CODEWITHSUNDEEP
sqldb2
user1613270
user1613270

Reputation: 533

How can I query records depending on value existance?

Assume I have a table with the following entries:

ID    VALUE    CATEGORY
-----------------------
 1       10    A
 1       20    S
 2       30    S
 2       10    A
 3       50    A
 4       40    C
 5       60    B

How do I write a SQL query so that for each ID if category S exists, it should output that record, but if it doesn't exist then it should output the entry which exists. So in my example I would like to achieve the following result:

ID    VALUE    CATEGORY
-----------------------
 1       20    S
 2       30    S
 3       50    A
 4       40    C
 5       60    B

Upvotes: 0

Views: 60

Answers (2)

user1613270
user1613270

Reputation: 533

I found a more elegant solution :-)

SELECT  ID, VALUE, CATEGORY
  FROM  (
      SELECT ID, VALUE, CATEGORY,
          ROW_NUMBER() OVER (PARTITION BY ID) as SEQ_ID
        FROM MY_TABLE
      ORDER BY ID, CATEGORY DESC
  )
 WHERE SEQ_ID = 1

Upvotes: 0

John Woo
John Woo

Reputation: 263703

try,

SELECT  ID, VALUE, Category
FROM    tableName
WHERE   Category = 'S'
UNION
SELECT  ID, VALUE, Category
FROM    tableName
WHERE   ID NOT IN
                (
                    SELECT  ID
                    FROM    tableName
                    WHERE   Category = 'S'
                )
ORDER BY ID

SQLFiddle Demo

Upvotes: 1

Related Questions