A little mySQL hustle here, cant figure something out!!!

[npsari]

 

Hi there,

 

I have table called Info.

 

There are two main colombs (Name, News)

 

I try display each distinct Name and his latest News:

 

$q = "SELECT DISTINCT Name FROM
info
ORDER BY Date ASC, Time ASC
LIMIT 10;";

$res = @mysql_query($q);

while($r = @mysql_fetch_array($res))
{	
print "$r[Name]";
print "$r[News]";
}

 

But the "News" dont show in Results!

 

I want the latest News of a Distinct Name to show too

 

What should i add to mySQL code guys

[taith]

a) distinct also should use group by

b) you need to "distinct(name), news"

[npsari]

I tried it:

 

$q = "SELECT DISTINCT(Name), News FROM
info
ORDER BY Date ASC, Time ASC
LIMIT 10;";

 

however, it Doesnt work!

 

It shows the names repeated too

[chigley]

Why can't you just do this:

 

<?php

$conn = mysql_connect("localhost", "user", "pass") or die(mysql_error());

$q = mysql_query("SELECT * FROM info ORDER BY Date ASC, Time ASC LIMIT 10;") or die(mysql_error());

while($r = mysql_fetch_array($q)) {
echo $r['Name']."\n\n";
echo $r['News']."\n\n";
}

?>

 

I always find it easier to select * and just fish out what you need.

[taith]

a) distinct also should use group by

[npsari]

Oh, sorry, I used the GROUP BY now

 

$q = "SELECT DISTINCT(Name), News FROM
info
GROUP BY Name
ORDER BY Date DESC, Time DESC
LIMIT 4;";

 

It arranges all Names DESC

 

But it doesnt show the latest News of the person DESC

It shows his First News

[chigley]

What is "Message" ~ you haven't mentioned that before?

[npsari]

Sorry, Message is News

 

And Chningly, i tried your code

It shows everything!!!

But thanks pal

[Barand]

@chigley

 

I always find it easier to select * and just fish out what you need.

 

For easier, read "lazier".

 

The query is less efficient, retrieving unnecessary data. And from a maintenance viewpoint when you or someone else reads it later, you cannot easily tell what the query is really for.

 

@npsari

 

SELECT n.name, n.news

FROM news n

INNER JOIN (SELECT id, MAX(CONCAT(date,time))as latest) as a

ON n.id = a.id AND CONCAT(n.date,n.time) = a.latest

[npsari]

I tried to explain my needs as much as I could

 

Anyway, i tried this code, it didnt show nothing this time

 

Its ok, i will try on it myself more

 

Thanks

[chigley]

Not necessarily lazy as I often then use other values from the table later in the code. I can see why it is interpreted as lazy though!

[Barand]

Sorry, my wife was nagging to go shopping and I forgot the FROM and GROUP BY in my haste

 

SELECT n.name, n.news

FROM news n

INNER JOIN (SELECT name, MAX(CONCAT(date,time)) as latest FROM news GROUP BY name) as a

ON n.name = a.name AND CONCAT(n.date,n.time) = a.latest