Selecting MAX from multiple fields

Anon

Hi there, I hope the subject isn't too misleading but it's late and my brain is non-functioning.

I've been playing around with this for a while and need some help.

There are two tables: Authors and Articles.

I am trying to select the highest article number (ar_id) from the Articles table depending on the Author id (a_id) in the Authors table.

So right now there are two authors in there, I want to only display the highest article id for each author. Any ideas?? I'm using mySQL and PHP.

I've been playing around with different variations of:

SELECT max(ar_id) FROM Articles, Authors WHERE Authors.a_id = Articles.ar_author

... but for some reason it just keeps displaying the max article, ignoring the authors.

Sorry for so long winded! Thanks.

Anon

I've got the same eprob too.

[deleted]

That's because you don't specify that you want to get the results for each author seperately. Use the GROUP BY clause:

SELECT max(ar_id), Articles.ar_author
FROM Articles,
Authors
WHERE Authors.a_id = Articles.ar_author
GROUP BY Articles.ar_author

Anon

Okay, I tried that as just a query and it does work -- it pulls out the highest article number per author. I also need to pull it out according to department, though (sorry, forgot this) and I tried combining the two statements:

SELECT max(ar_id), Articles.ar_author, Articles.ar_title, Articles.ar_short, Departments.d_id
FROM Articles, Authors, Departments
WHERE Authors.a_id = Articles.ar_author
AND Articles.ar_dept = 1
AND Departments.d_id = 1
GROUP BY Articles.ar_author

The problem now is that when I do the query, it DOES return the highest ar_id but for some reason the info about the article is being pulled from a completely different article. As in, the max(ar_id) shows up at 14 but the info it displays about the article is from article 2.

Also, I can't figure out how to put max(ar_id) as a variable that I can call later...

My brain hurts. 🙁
there has to be a less complicated way than this?

[deleted]

JOINs require that you somehow link the tables together.
The authors and articles are linked by author.a_id = author.ar_author
You need to do the same to link departments to either articles or authors.

If you don't, then SQL will not be able to work out which departments you mean, and it will return all departments.
This is called a cartesian product, you get all departments once for each result from the rest of the query. (if you have 10 results, and 10 departments, you will get 100 rows back)