Jump to content

Trying to display unique rows?


Recommended Posts

I have a MySQL table like this:


id  thread name

1  1          bob

2  2          bob

3  3          bob

4  3          bob

5  3          bob


I want to loop through the entire table, but only show the most recent thread. So there is 3 threads that all have the number 3, I want to display the latest one in the the table (not all 3).  I was looking into DISTINCT but it was not working, anyone have any ideas?


What I have right now (just looping through the table):


$f_q = mysql_query("SELECT * DISTINCT thread FROM jos_kunena_messages");
while($f_r = mysql_fetch_array($f_q)){
$id = $f_r['id'];
        $thread = $f_r['thread'];
        echo $thread;

Link to comment
Share on other sites

I want to display the latest one in the the table


mySql is a Relational database, it is not sequential. There is no concept of the "latest row". In theory, when you do not specify an ORDER BY, the server is free to return the rows in any order it wants, and that order could be different at different times. So, in order to answer your question, we need to know how you define "the latest row" in this particular table.


By the way, SELECT * DISTINCT ... is invalid syntax, that syntax should be SELECT DISTINCT * .... Note: this will NOT solve your problem. I suspect the solution is going to require a subquery. Perhaps something along the lines of SELECT * FROM myTable WHERE ID = (SELECT MAX(ID) FROM myTable). But until we know how to determine the "latest row" (and possibly the table structure), we can't be sure of the solution.

Link to comment
Share on other sites

I guess I explained it badly. But its for a Forum (Just like these ones). I want to show the most recent post (but I do now want to show 3 post from the same thread). If you go and look at http://www.chicago3media.com/ you will see "Why gas is so expensive" and "Re: Why gas is so expensive" when I only want to display the most recent one which would be "Re: Why gas is so expensive".


Now each post, has a thread id (so that means both those post I said above would have the same thread id), so I was thinking if I can just only display the last post in that thread  (using order by DESC) and DISTINCT?


Did I explain it any better?

Link to comment
Share on other sites

  • 3 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.