Help on DISTINCT

twopeak

I would like to get some help on the DISTINCT 'option' in a SQL query.
What I need to do is this:

select from a phpbb table (phpbb_posts) the last 5 records, but I don't want to select a record if another record has the same topic_id

This would be a (simplified) example:
I do a select, ordered by the date
sql orders everything by date, takes the first record and nottices topic_id=3
then it selects the next topic and nottices topic_id=5
then it selects the next topic and nottices topic_id=4
then it selects the next topic and nottices topic_id=3 -> oops, I allready have a topic_id=3, so I leave out this row
then it selects the next topic and nottices topic_id=10
then it selects the next topic and nottices topic_id=3-> oops, I allready have a topic_id=3, so I leave out this row
then it selects the next topic and nottices topic_id=4

I hope it's clear what I want?
I checked mysql.org and this is the explanation I get:

The options DISTINCT, DISTINCTROW and ALL specify whether duplicate rows should be returned. The default is (ALL), all matching rows are returned. DISTINCT and DISTINCTROW are synonyms and specify that duplicate rows in the result set should be removed.

This is what I tried

$query = "SELECT post_id, DISTINCT topic_id, forum_id, poster_id, post_time WHERE forum_id='1' OR forum_id='3' OR forum_id='5' FROM phpbb_posts ORDER BY post_time DESC LIMIT 5";

tomhath

You're on the right track.

Pay close attention when reading documentation, the DISTINCT applies to the entire row, not just a single column.
Think of the rows returned as a set of data, each row is one member fo the set. This is how i'd write it:

$query = "  

  SELECT  DISTINCT post_id, topic_id, forum_id, poster_id, post_time 
  FROM phpbb_posts 
  WHERE forum_id in ('1', '3', '5') 
  ORDER BY post_time DESC 
  LIMIT 5";

twopeak

but only topic_id has to be distinct (unique)
So I could write it in 2 querys, like this:

SELECT
DISTINCT topic_id
FROM phpbb_posts
WHERE forum_id in ('1', '3', '5')
ORDER BY post_time DESC
LIMIT 5";

And with those results
I would do something that takes every single record out of the database!!!
Thanks!

Is there a way to do this in 1 myslq query? (I always like complicated solutions to easy problems 😉 )

tomhath

It depends on what you are doing. If you need the list of distinct topic_ids first, then all the rows in the database, you could do it in two queries.

If you want to do it in a single query you'll have to decide what happens when the same topic_id appears in several different rows.

twopeak

I'd prefere it in 1 query.
I finished it with two querys, but it would be a lot nicer in 1 query.

The records are ordered by date DESC, so if it gets a second time a topic_id, it should just be left out.

But I do have the impression it is not possible to do this...

arijuanma

Hello,
Have you tried with GROUP BY?

Example:
SELECT post_id, topic_id, forum_id, poster_id, post_time
GROUP BY topic_id ORDER BY post_time desc;

I hope it will work

twopeak

I do not understand the workings of the group by option.
I haven't searched it in the mysql docs yet, but it seemed to me it would group all the topics together, giving me an easyer way to select the rows I would like to keep.

But instead of this, it leaves out rows, and mixes them through eachother, not grouping the topic_id's together, but at first sight it seems random!
And it leaves out many rows.
If I look at the result of the simple query with just filtering out the forum_id's I have +300 rows
If I add the group_by, I only keep 23 rows!

I don't get it, I'll just leave everything the way it is,
my users will have to use their own intelligence, and courage to browse to the forum themselves 🙂
Thanks for all the appreciated help!