Using Max(Count(*)) query

[gj0519]

I have a query I wrote in Oracle but I can not seem to get it to work in MS SQL 2005.
I need to get the name of the author with the most orders.
The query below gives me the name and the max count, but I only want to see the name. That is where I am hung up.

(Select  fname, Max(TotalB) as Total2
		From 
			Select a2.fname, count(*) as TotalB
				from author a2 join bookauthor ba
				on a2.authorid = ba.authorid join books b
				on ba.isbn = b.isbn join orderitems oi
				on b.isbn = oi.isbn
				Group by a2.fname)Tbl
				Group By fname)

Thanks,
GJ

 

[gmmastros]

Select  fname, Max(TotalB) as Total2
        From
            (
            Select a2.fname, count(*) as TotalB
                from author a2 join bookauthor ba
                on a2.authorid = ba.authorid join books b
                on ba.isbn = b.isbn join orderitems oi
                on b.isbn = oi.isbn
                Group by a2.fname)Tbl
                Group By fname) As AliasName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gj0519]

Thanks for the reply, what I am after is the author with the highest total, I re-wrote the query.

Select count(oi.order#) as Total, a.fname, a.lname
from author a join bookauthor ba
on a.authorid = ba.authorid join books b
on ba.isbn = b.isbn join orderitems oi
on b.isbn = oi.isbn
Group By a.fname, a.lname

The results I get:

12	JACK	BAKER
7	JAMES	AUSTIN
6	JANICE	JONES
6	JUAN	ADAMS
1	LISA	PORTER
2	LISA	WHITE
5	OSCAR	FIELDS
8	ROBERT	ROBINSON
1	SAM	SMITH
6	TINA	PETERSON
2	WILLIAM	WHITE

Thanks,
GJ

 

[ShankarJ]

Hi!

Select [red]TOP 1[/red] count(oi.order#) as Total, a.fname, a.lname
from author a join bookauthor ba
on a.authorid = ba.authorid join books b
on ba.isbn = b.isbn join orderitems oi
on b.isbn = oi.isbn
Group By a.fname, a.lname
[red]ORDER BY 1 DESC[/red]

Regards

 

[ESquared]

Select count(oi.order#) as Total, a.fname, a.lname
from author a join bookauthor ba
on a.authorid = ba.authorid join books b
on ba.isbn = b.isbn join orderitems oi
on b.isbn = oi.isbn
Group By a.fname, a.lname
having count(oi.order#) = max(count(oi.order#))

This will get ties as well (items sharing the highest order count)

And if your order# can be null it won't be included in the count...