How to Order these Records?

[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

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:

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

 

[Patrice]

ORDER BY COUNT(Document), MAX(DocDate) DESC ?

--

 

[Scott Allen]

Hi Miguel:

You can specify more than one column in the ORDER BY clause to nest
the sorts.

I'm not sure what your exact schema looks like, but if you are using
SQL Server and have the sample pubs database, you can see how the
following query works the way you need:

SELECT a.au_lname, a.au_fname, COUNT(t.title_id), MAX(pubdate)
FROM authors a
INNER JOIN titleauthor ta ON
ta.au_id = a.au_id
INNER JOIN titles t ON
t.title_id = ta.title_id
GROUP BY
a.au_lname, a.au_fname
ORDER BY
COUNT(t.title_id) DESC,
MAX(pubdate) DESC