如何使用SQL每个值限制项目的选择？ [重复]

Question

This question already has an answer here:

• How to SELECT the newest four items per category? 8 answers

Ok, so I have this private messaging system I'm working on, and here's kind of how it's organized

ID From-ID To-ID Message Thread
1 0 1 "blah blah blah" 1
2 0 1 "blah blah blah, blah!" 1
3 0 3 "hey <3>, how are you? Signed, <0>" 2

The way it's like this is so that you can have multiple messages in a thread, so you can reply.

Then on the left side of my private messages page, you can select which thread to look at.

So, it'll be a button that says the sender's name, so I used this SQL:

mysql_query("SELECT * FROM pms WHERE to-id='".$_SESSION['id']."' ORDER BY id ASC");

But, I want it to be something like (pseudo-code)

mysql_query("SELECT * FROM pms WHERE to-id='".$_SESSION['id']."' LIMIT 1 per `thread` ORDER BY id ASC");

So that it will only show one link to a thread.

What's happening now though is that it will show a new button, every time there is a reply to the thread. So, if there are 100 replies to message, and they're all in the same thread (since they're "replies") there will be 101 buttons, which is not ideal, for obvious reasons.

Everything is currently working, except for that button limitation that I'm talking about.

</div>

Answer 1

First things first... your ID column... shouldn't it be auto-incrementing? Just checking...

If you want to get all the threads, you might want to do something like...

Select distinct thread from pms

-- then optionally...
where to-id = '".$_SESSION['id']"' order by ID asc

That way, you'll just get the first instance of that each thread.