MySQL - distinct values from two columns

Question

I have a table with multiple columns from which I need to get unique values from two of them.

The table looks something like this

msgid    sender    receiver  payload
1        service1  service2  xxx
2        service1  service3  xxx
3        service2  service3  xxx
...

The desired output should be: service1, service2, service3 (distinct values from columns sender and receiver)

I found many different approaches on how to do this and chose using UNION like this:

String query = "(SELECT sender AS service FROM messages) UNION " +
        "(SELECT receiver AS service FROM messages) ORDER BY service";

I tried also

String query = "(SELECT DISTINCT sender AS service FROM messages) UNION " +
        "(SELECT DISTINCT receiver AS service FROM messages) ORDER BY service";

which should produce the same since UNION already returns unique values. However, I am getting mysql exception:

java.sql.SQLException: Column 'sender' not found.

But column sender exists! When I run

String query = "SELECT DISTINCT sender FROM messages";

everything works just fine. Could you tell me, please, what am I doing wrong? I tried to use similar statement as this post suggested. Thanks!

EDIT: this is the EXACT query I am using right now:

String query = "SELECT sender AS service FROM messages UNION SELECT receiver AS service FROM messages ORDER BY service";

This query should work as mentioned in the comments. However, I still get the exception. Why so? Thanks for troubleshooting...

My mysql version is 5.6.

EDIT2: I had no idea that the problem could be with JDBC and not my statement. I apologiye for not providing this information in my question...

yes union is to get distinct value then why u r using distinct.. — Rahul
– Rahul, Commented May 30, 2013 at 7:50
as I pointed out in my question, I have tried both statements and they both produce the same exeption... — Smajl
– Smajl, Commented May 30, 2013 at 7:51
@Smajl ok , a guess in the dark is when you retrieve the data you use "sender" and not "service" that could explain why the first 2 queries work and the last doesn't. let me know if this is the case — Stephan
– Stephan, Commented May 30, 2013 at 7:55

Stephan · Accepted Answer · 2013-05-30 08:10:07Z

2

A guess in the dark is when you retrieve the data you use "sender" and not "service" that could explain why the first 2 queries work and the last doesn't.

Let me know if this is the case.

Assuming you are using JDBC:

    String query = "SELECT sender AS service FROM messages UNION SELECT receiver AS service FROM messages ORDER BY service";
    ResultSet rs = ps.executeQuery(query);
    while (rs.next()) {
        String value = rs.getString("sender"); // instead it should be "service"
        // Some other actions
    }

edited May 30, 2013 at 8:10

answered May 30, 2013 at 8:02

Stephan

8,1103 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Smajl Over a year ago

What do you mean by his? "sender" and "receiver" are the names in my table. "service" is just an alias I use in the statement with union...

Stephan Over a year ago

i mean on code side when you retrieve the data , see my updated answer

Smajl Over a year ago

Thanks! I had no idea that the problem would be with JDBC and not my statement... I apologize for not providing this information to my question...

gbn · Accepted Answer · 2013-05-30 07:58:07Z

1

It should be this simple because UNION already removes duplicates

SELECT sender AS service FROM messages
UNION
SELECT receiver AS service FROM messages
ORDER BY service;

The MySQL docs for UNION agree with this and my sort here

Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY

On SQLFiddle my query works http://sqlfiddle.com/#!2/c64e4/7 and does the OPs http://sqlfiddle.com/#!2/c64e4/8

edited May 30, 2013 at 7:58

answered May 30, 2013 at 7:49

gbn

435k84 gold badges602 silver badges690 bronze badges

7 Comments

Mifeet Over a year ago

I think the point of the question is that the query doesn't work for Smajl for whatever reason

Smajl Over a year ago

String query = "SELECT sender AS service FROM messages UNION " + "SELECT receiver AS service FROM messages ORDER BY service"; - I have already tried this one, see my question.

gbn Over a year ago

@Smajl: in the question you have brackets. Now, please post exact queries please

gbn Over a year ago

@Smajl: check my SQLFiddle link. And your query works too sqlfiddle.com/#!2/c64e4/8

Smajl Over a year ago

@gbn I trust you, that the posted query is ok. However, it does not solve my problem. So basically my question would be "Why is it not working for me?"

|

Kingshuk Deb · Accepted Answer · 2013-05-30 08:35:46Z

0

just delete the "+" sign and "Distinct" from ur code and try..

answered May 30, 2013 at 8:35

Kingshuk Deb

1,7393 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL - distinct values from two columns

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related