[RESOLVED] using DISTINCT

simon622

Hi ppl.

Im using mysql 3.23

I need to run a query, to retrieve a subset of data at db level.

I have a table;

msg(PK_msg_id,to_id,from_id)

and I need to select all rows where a to_id is equal to a known value, and where the from_id is distinct. For example there could be 10 rows with the correct to_id but some of them may have more than 1 from_id of the same value.

So altho I know its not correct somehow I need to run:

select * from msg where to_id = "1" and DISTINCT from_id

toplay

I'm not sure I completely follow. Like I don't know if you expect to get one row back only. Well, with this SQL, you could get back more than one row:

SELECT DISTINCT from_id FROM msg WHERE to_id = '1'

simon622

thanks for your reply. Thats the whole problem, distinct only seems to work on the primary key field. Therefore a foreign key can be there multiple times. I tried your syntax, and got returned 2 rows both with the same from_id. I need a result set, with any msg_id, one particular to_id and distinct from_ids. Its a real headscratcher!

toplay

Well, I don't think I'm going to get a clear picture unless you show some example data in your table and an example of the result you want.

I don't understand why DISTINCT would give you back two identical values, but if you don't care which one you get, you can limit the output to one:

SELECT DISTINCT from_id, PK_msg_id FROM msg WHERE to_id = '1' LIMIT 1

Or this way maybe:

SELECT DISTINCT from_id, PK_msg_id FROM msg WHERE to_id = '1' GROUP BY from_id

I'm not aware of DISTINCT only working on a primary key.

See:
http://dev.mysql.com/doc/mysql/en/distinct-optimization.html

EDIT:
I moved the DISTINCT part first, right after the SELECT to make the syntax right.

The first query will return one row back, while the second will return all the unique from_id's (could be more than one row). You can even take out the DISTINCT on the second query and you'd get the same results (because DISTINCT and GROUP BY do the same thing).

Lars_Berg

You can't use distinct that way, it will cause a syntax error.

To get only one msg_id for each distinct from_id

select min(msg_id),from_id
from msg
where to_id = 1
group by from_id

toplay

Lars Berg wrote:
You can't use distinct that way, it will cause a syntax error.

To get only one msg_id for each distinct from_id
select min(msg_id),from_id
from msg
where to_id = 1
group by from_id

I edited my previous post to correct the syntax (by putting DISTINCT first). But my second query listed will produce the same results as yours Lars.

Like I said simon622, you should post some actual data from the table, and show us an example of the results you want. Or did you fix this problem already?

Please note I'm working on MySQL version 4+ while I see you list v3.23.

simon622

thanks lars, the combination of group by and max() did the trick! nice one, thanks everyone