-1

So I have this

For every username there is at most one unique associated not null col2 value and potentially some null col2 values.

+----------+-------+
| username | col2  |
+----------+-------+
| a        | 1r1   |
| a        | 1r1   |
| b        | 1z2   |
| b        | null  |
| c        | 1t3   |
| c        | null  |
| c        | 1t3   |
| d        | null  |
+----------+-------+

And I'm looking for an output like this:

+----------+------+
| username | col2 |
+----------+------+
| a        | 1r1  |
| b        | 1z2  |
| c        | 1t3  |
| d        | null |
+----------+------+

The first colums are names of companies and the second are the ID but sometimes (user error input) there is a few companies that doesnt have ID, and i want all the companies names with their posible ID's

I believe it could be with coalesce but I'm stuck...

Thanks for the help

  • 2
    What if there are 2 rows with same username but different col2 values? Say, d 120 and d 121? – ypercubeᵀᴹ Jan 22 at 19:27
  • thank god the db i have thats not the case @ypercubeᵀᴹ – Ignacio Correa Moreno Jan 22 at 19:28
3

You can use GROUP BY and either MIN() or MAX() to get rid of the nulls and show one row per username:

SELECT username, MIN(col2) AS col2
FROM tableX
GROUP BY username ;

or remove them in advance and use DISTINCT:

SELECT DISTINCT username, col2
FROM tableX
WHERE col2 IS NOT NULL ;

The two queries above will show same results, unless there are usernames with only nulls or with different (non-null) col2 values.

  • i forgot to put it on the question, but i also have values that have only nulls in the col2. The first colums are names of companies and the second are the ID but sometimes (user error input) there is a few companies that doesnt have ID, and i want all the companies names with their posible ID's – Ignacio Correa Moreno Jan 22 at 19:35
  • 2
    If you want those to appear in the result (with 'username' - NULL) then use query 1. If you don't want them to appear at all use query 2. – ypercubeᵀᴹ Jan 22 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.