Trying to display unique rows?

[antfuentes87]

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):

 

<?php
$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;
}
?>

[Pikachu2000]

The obvious questions: Why do you have more than one thread number 3? Is there a timestamp in each record?

[ryanfilard]

_

[ryanfilard]

Try This:

$f_q = mysql_query("SELECT * DISTINCT thread FROM jos_kunena_messages ORDER BY id DESC");

[antfuentes87]

Its from a forum table. I want to just the newest one from each different thread.

 

http://www.chicago3media.com/

 

You can see it here, where it says "Why gas is so expensive" and "Re: Why gas is so expensive". I just want it to say "Re: Why gas is so expensive". It was a bad example sorry. Hope this makes more sense.

[ryanfilard]

Did you try the "ORDER BY id DESC"

 

It works here:

 

http://ryanweekly.com/user/index.php?p=ryanweekly

[antfuentes87]

Did you try the "ORDER BY id DESC"

 

It works here:

 

http://ryanweekly.com/user/index.php?p=ryanweekly

 

I tired exactly what you had in the above post and I get a error.

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/c3m/public_html/templates/sunsetter/pages/home.php on line 9

[Pikachu2000]

That query won't execute, it isn't syntactically correct.

[antfuentes87]

That query won't execute, it isn't syntactically correct.

 

So what is the correct way?

[Pikachu2000]

The obvious questions: Why do you have more than one thread number 3? Is there a timestamp in each record?

[ryanfilard]

mysql_query("SELECT * FROM yourtable ORDER BY id DESC")

[antfuentes87]

mysql_query("SELECT * FROM yourtable ORDER BY id DESC")

 

How does that help? That just displays all the rows?

[DavidAM]

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.

[antfuentes87]

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?

[antfuentes87]

I figured it out guys. Thanks for the help. The solution was:

 

SELECT DISTINCT * FROM jos_kunena_messages GROUP BY thread ORDER BY thread DESC LIMIT 5

[fenway]

Yeah, but mixing DISTINCT, *,and GROUP BY is likely very bad.