Only return unique values

[Nixon]

Hi all,

I need to construct a mysql query that only returns rows with unique values, for example, I have a query as follows:

select * from forum where username=jack

now say I get these results:

id      username      topicid      time

7       jack               22            101
18     jack               24            103
22     jack               22            156

Now say I only wanted to print this information to a table, how I would I print only line 1 and line 2, and not line 3 because it has the same value as line 1 in the topicid coloumn, am i making this clear enough?

Any help or suggestions would be much appreciated!

Jack.

[Sxooter]

does it matter which row you get back, or would 2 and 3 be as good as 1 and 2?

If you want the lowest values, you could do something like:

select min(id), username, topicid, min(time) from forum group by username, topicid

But note that the id and time MAY NOT line up. What version of MySQL are you using, and can it do subqueries?

[Nixon]

Hi, no it does not matter which one it retrieves, the situation im in is trying to pull what forum topics a user has had a reply in, the main topic is in 1 table and the replies are in a 2nd table, so i need to find out what main topics the user has replied to, through 'topicid' so I can list the subscribed posts, but i dont want to list the forum topic twice if the user has posted twice into that forum topic lol! Hope this helps!

Thanks, Jack.

P.S - 4.0.22-standard (MySql Version)

[Sxooter]

If that version does subqueries, then you could use something like:

select id, username, topicid, time from forum where id in (select min(id) from forum group by username, topicid)

Some versions of MySQL support the non-standard usage where you don't have to aggregate the select columns not found in the group by, so this may be equivalent, but non-portable (and possible not supported in the future) on your system:

select id, username, topicid, time from forum group0 by username, topicid

Although, that may have the same non-deterministic behaviour as my first query, ie, id and time may not be from the same rows with the same username/topic combo.

[LordShryku]

From another subquery question from earlier, I can say that MySQL didn't support subqueries until 4.1.

Just as an observation though, do you really need all fields in this query? If the purpose was to just see which topic id's had replies and then maybe to link them, I'd think you could get away with selecting only distinct topicid's.