Never solved problem

Anon

Hi.

I asked a question on this forum about 1 year ago, bu didn't ge an answer then.

Maybe I'm more lucky now?

The question follows:
"Hi.

I have several articles in a database table, written by 5 different writers and at different dates (all writers have written several articles). I want to group all the articles by the writer and each group shall be ordered by date desc.
My query looks at follows:

select writer , date from articles group by writer order by date desc

This will result in the groups being ordered by date desc, but the records inside the groups won't (they will be ordered as standard: the oldes article first).
Does anyone understand me? =)
Please help...

/Martin"

amcgrath

have you tried select writer , date from articles group by writer,date order by date desc ?

btubalinal

Allen McGrath wrote:

have you tried select writer , date from articles group by writer,date order by date desc ?

I don't think that will work if the dates inside the date field are unique. With data like the following:

+---------+---------------+
| writer | date |
+---------+---------------+
| author1 | 20020105123055|
| author2 | 20020106113005|
| author3 | 20011230072555|
| author1 | 20020111114510|
| author2 | 20020118123428|
| author3 | 20011030084052|
+---------|---------------+

all rows will be returned because each row is unique, i.e. there are no 2 or more rows that have BOTH the writer and date columns containing the same values.

What will work for data like this is a query like:

SELECT writer, max(date) from articles group by author

No need for the order by clause but you could add it on for s's & g's.

Hope this helps and gives you some insight.

amcgrath

Yea, I agree, to be honest I can't for the life of me figure out why hewants what he wants, or if any suggestion is good ...

Anon

"Group by" is used to group aggregates. Since you have unique dates for the writers, it's not doing you any good. Here's an example where it would work for you. Let's say you wanted the number of articles each writer has written. You'd do "select writer, count(*) from articles group by writer".

But since each writer has submitted articles on different dates there's nothing to aggregate. You have to select all the dates along with the writers.

Try

select writer, date from articles order by writer asc, date desc

Hank

Anon

Hi.

I only want the latest article from every writer.

If the database is as follows:

id | writer | date | headline
1 | elvis | 99-07-25 | First elvis
2 | joe | 99-08-12 | First joe
3 | elvis | 99-09-13 | Second elvis
4 | jenny | 00-01-02 | First Jenny
5 | joe | 00-03-15 | Second Joe
6 | philip | 01-05-30 | First Philip

I want the following to be displayed:
<a href="id=3">Second Elvis</a>
<a href="id=4">First Jenny</a>
<a href="id=5">Second Joe</a>
<a href="id=6">First Philip</a>

I need to group by the writer.

amcgrath

Then max() on the date sohuld do it for ya, no need to group/....