How to Order these Records?

  • Thread starter Thread starter Miguel Dias Moura
  • Start date Start date
M

Miguel Dias Moura

Hello,

i have this SQL code in my dataSet (I am working in ASP.Net / VB):

SELECT Author, COUNT(Author) AS totalDocuments
FROM documents
GROUP BY Author
ORDER BY COUNT(Author) DESC

What i want to do is this:
When there are 2 Authors which published the same number of documents, i
Click to expand...
want to sort them acording to the date they published their last documents.
There is a "Date" field associated to each document. So i also need to load it.
An Example:
Click to expand...

Author totalDocuments Last Document Published - Most Recent
Document Date associated with that Author
John 4 10/05/2004
Michael 3 10/07/2004
Grace 2 25/06/2004
Mary 2 16/05/2004
Andrew 2 10/04/2004
Peter 1 18/05/2004

See, first the Authors are ordered by "totalDocuments" = Number of published
documents.
When 3 Authors published the same number of documents they are ordered
acording to each one published the most recent document.

Can you help me out?

Thank You Very Much,
Miguel
 
.. . .
What i want to do is this: i
want to sort them acording to the date they published their last documents.
load it.
Click to expand...

This should do the trick ...

SELECT Author
, COUNT(Author) AS totalDocuments
, MAX( [Date] ) AS lastPublished
FROM documents
GROUP BY Author
ORDER BY COUNT(Author) DESC
, MAX( [Date] ) DESC

BTW; "Date" as a field name? Bad Idea, IMHO.

HTH,
Phill W.
 
Back
Top