need some help with a mysql query

23style

i have a table that holds news articles. for example:

news_id 1 is for client Bob
news_id 2 is for client Bob
news_id 3 is for client_Dave
news_id 4 is for client_Jim

i would like to select the highest news_id for Bob and display the result in an html page, having some trouble with the sql though.

i know this is probably simple but i cant figure it

any help is much appreciated

thanks 🙂

vallette

This should work:

SELECT TOP 1 news_id FROM News WHERE client='bob'
ORDER BY news_id

amchargue

Originally posted by vallette
This should work:

SELECT TOP 1 news_id FROM News WHERE client='bob'
ORDER BY news_id

Or,
SELECT MAX(news_id) FROM News WHERE client='bob'
ORDER BY news_id

vallette

FYI: if you choose to use MAX there's a couple of things to be aware of.

First, if you select any other columns you'll need to include a GROUP BY clause

SELECT MAX(news_id), article_title WHERE client='bob'

will throw an error use:

SELECT MAX(news_id), article_title WHERE client='bob'
GROUP BY article_title

Which brings me to second thing. In the example I gave above every record for bob would be returned if each title was unique. The results would be grouped by titles that match and then the record with the highest news_id would be returned from that group. If every title was unique each group would only have one member so every news_id would be the highest for it's group.

23style

cool, that query works in my sql program

1 question tho:

how would i access the content of the news post?

i tried this...but it doesn't display anything.

$query = "SELECT MAX(news_id) FROM news WHERE for_client='$client' ORDER BY news_id";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

for ($i=0; $i<$num_rows; $i++) {
$row = mysql_fetch_array ($result);
$news_content = $row['news_content'];

echo $news_content;

}

vallette

You aren't selecting 'news_content' in your query. Assuming that the content of 'news_content' is unique I'd probably just use:

SELECT TOP 1 news_content FROM news WHERE client='bob'
ORDER BY news_id

that way you won't be pulling back every article for the client (see my second post)

amchargue

need to include the news_content field in your SELECT and include the GROUP BY like Vallette said -- I can never remember if ORDER BY comes first or GROUP BY -- something like

$query = "SELECT MAX(news_id),news_content FROM news WHERE for_client='$client' GROUP BY news_ID ORDER BY news_id";

btw, if Val is reading, I can't get a SELECT TOP 1 query to work in MySQL. Can you? I wonder if it's a version thing? I have seen that in Access I think.

amchargue

actually I guess you wouldn't need ORDER BY if you're only expecting one row in the recordset -- there's nothing to order ....

vallette

You're right TOP isn't valid in mySQL--I'm a mySQL fan but use SQLServer at work. Just use:
SELECT news_content FROM news WHERE client='bob'
ORDER BY news_id

You don't need the MAX function since it requires that you GROUP BY news_content which will return just as many rows with added overhead.

23style

when i include news_content as in: $query = "SELECT MAX(news_id),news_content FROM news WHERE for_client='$client' GROUP BY news_id";

it returns both the two news posts that exist for that client. i want the highest news_id for that specific client

man..i am terrible at sql queries. haha

😉

23style

wait..never mind, i figured it out

thanks for all your help guys 😃