CODEWITHSUNDEEP
postgresql
mada
mada

Reputation: 111

Need help for a query

I have this table

NAME|VALUE|
T   |  A
T   |  NONE
T   |  B
S   |  NONE

where the value NONE is a NULL value in real.

I need to do a query who will return all lines BUT if th name have a value i should ONLY return the line with his value like this in result for the table above:

NAME|VALUE|
T   |  A
T   |  B 
S   |  NONE

Do you know to do such query?

Thanks in advance for your help.

Upvotes: 2

Views: 97

Answers (2)

Matthew Wood
Matthew Wood

Reputation: 16417

Is there a max of two values? That is, is only possible to get a single value, "NONE", or one of each. If so, you can do this:

SELECT name, COALESCE(MAX(NULLIF(value, 'NONE'), 'NONE')
FROM <tablename>
GROUP BY name

I believe the following will work for the general case (one or more non-NONE values possible):

SELECT name, value FROM <tablename> WHERE value <> 'NONE'
UNION
SELECT name, value FROM <tablename> WHERE value = 'NONE'
  AND name NOT IN (SELECT name FROM <tablename> WHERE value <> 'NONE')

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 837966

Here's one way you could do it:

SELECT T1.NAME, T2.VALUE
FROM (SELECT DISTINCT name FROM Table1) AS T1
LEFT JOIN (SELECT * FROM Table1 WHERE VALUE IS NOT NULL) AS T2
ON T1.NAME = T2.NAME

Results:

S   NULL
T   A
T   B

Upvotes: 1

Related Questions