[PostgreSQL] Need help for a query.

Question

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.

Answer 1

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')

Answer 2

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