By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,234 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,234 IT Pros & Developers. It's quick & easy.

How to Order these Records?

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Miguel Dias Moura" <we****@27NOSPAMlamps.com> wrote in message
news:uG**************@TK2MSFTNGP10.phx.gbl...
.. . .
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.


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.
Nov 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.