Limiting MySQL Results

How to filter dupes?

[Nick_W]

Hi all,

Is there some great MySQL function of flash of insight someone could recommend for the following scenario?

I have a table where I need to select and order rows by timestamp field. In this talbe I have a col called 'start': it can be 1 or 0.

I'd like all the rows ordered by date BUT only if they have a start of 1

id ¦ title ¦ desc ¦ date ¦ start

(or somthing similar)...

It's easy to get a list of 'topics' (it's for forum software) as start=1 means theat it's the first post, but I'm trying to get it ordered by last reply which means currnetly I get dupe values as I cannot restrain it by 'start = 1' .

Anyone following that? ;-)

Nick

[dmorison]

Not sure I quite follow that, Nick, is "SELECT FROM table WHERE start='1' ORDER BY DATE" not what you're after?

[ukgimp]

is DISTINCT what you are after in the sql Nick?

[Nick_W]

dmorrison,

Sorry, let me clarify: The records that have start=o (replies to topics) are what I want to order by (the most recent posts)...

>DISTINCT

I'll go have a look ;-)

<added>for anyone thinking of re-doing the table, it's just an example ;) the actual setup is more complex but this illustrates the point reasonably...</added>

Nick

[jatar_k]

I dont totally get it.

You want to do something like the "active list" here? Only select the most recent response from every thread and then order them by date? If so, is there a field which tells you what thread it is from?

[jaski]

I may not have fully understood the problem .. but some thing on the following lines should probably be your schema.

I think you need to have two tables.
Say Threads and Messages... where threads is the master table and messages is the detail table.

Threads
thread_id¦subject¦description

Messages
message_id¦thread_id¦message¦start¦timestamp

Now if this schema looks OK for what you are writing...do you need to select threads with latest posts... or is it some thing else..

[dmorison]

The records that have start=o (replies to topics) are what I want to order by

Then that's just a case of:

"SELECT * FROM table WHERE start='0' ORDER BY DATE"

although i'm probably still not totally getting you...

[Nick_W]

Okay, sorry to confuse everyone, here is the real schema:

CREATE TABLE postcontrols (
  postid INT UNSIGNED NOT NULL,
  start TINYINT(1) UNSIGNED NOT NULL,
  date TIMESTAMP NOT NULL,
  parent INT UNSIGNED NOT NULL,
  author INT UNSIGNED NOT NULL,
  INDEX(start),
  INDEX(date),
  INDEX(parent),
  PRIMARY KEY(postid)
);

The postid connects to a table containing the text of a post, the parent refers to the thread that the post belongs to.

If start is '1' then it is the first post of a thread, ohterwise it is a reply.

I can get an index of threads ordered by the date the thread was started, but when I try to get the 'active list' effect, I get many dupe values....

Does that make it clearer? ;-)

Nick

[DrDoc]

SELECT * FROM postcontrols WHERE start='0' ORDER BY date DESC GROUP BY parent

[Nick_W]

Don't you sleep Doc? ;-)

Looks like I need to go read up on this new fangled 'group by' thingy....

Nick

[DrDoc]

Talking in my sleep ;)

[DrDoc]

GROUP BY [mysql.com]

[Nick_W]

Hmmm... I can get the group by in the statement *before* the order by, but not the other way around...

So if I have

GROUP BY parent
ORDER BY date DESC LIMIT 3.30

I get no dupes but no 'ative list' order either...

and

ORDER BY date DESC
GROUP BY parent LIMIT 3.30

does not work....

Am I doing somthing wrong here?

Nick

[DrDoc]

... GROUP BY parent, ORDER BY date DESC, LIMIT 3,30

Maybe?

[Nick_W]

you mean with the commas?

Nope, it doesn't like it....

Nick

[DrDoc]

SELECT * FROM postcontrols WHERE start='0' GROUP BY parent ORDER BY date DESC

You said that you get no dupes, but no active list order either... So, you get the correct list at least?

[Nick_W]

Yep, correct list ;-) No dupes...

If I take the group by clause out then I get dupes but the order (active) we need...

Nick

[DrDoc]

And if you take out DESC... no difference, eh?

[Nick_W]

Nope, none...

Nick

[DrDoc]

Try adding "AND date IS NOT NULL" to the WHERE clause.

Not that date will ever be null, but it should help MySQL's ordering when the ORDER BY key is used in the WHERE statement...

[DrDoc]

Try to EXPLAIN the query and see if you get "Using filesort" in the "extra" column.

[Nick_W]

Actually, I tell a lie!

The DESC thing does change it. I'm getting threads in the order of when the thread was started at the moment. If I take out DESC, I get them the other way round.

Nick

[DrDoc]

Ah! So, then it does sort it descending... just after when the thread was started, not the last reply to it ;)

[Nick_W]

Just to recap, here's the WHERE part of the query:

WHERE threadcontrols.forum = '1' AND threadcontrols.threadid = threads.id AND postcontrols.parent = threads.id AND threadcontrols.status > 2 AND postcontrols.author = members.id GROUP BY postcontrols.parent ORDER BY postcontrols.date DESC LIMIT 0,20

Nick

[DrDoc]

What is stored in postcontrols.date? Is it really the last reply to the thread?

[Nick_W]

It's all replies to a thread. From the start to the last...

Nick

[DrDoc]

huh? I thought you said that the column was of type timestamp?

Or, you mean just that for each post the time is stored? Well, that's what I meant too ;)

But it only sorts after when it was started?

[Nick_W]

Correct on all counts.

If I leave the group by out of it, I get the dupes, but they are in the order of last reply...

Nick

[DrDoc]

Try to order by post id instead (I assume that the higher the id, the more recent the post)...

[Nick_W]

Only if I don't make too many deletes on the table ;-)

It's an auto_increment field...

Nick