Join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join help

Author

Topic

k80sg
Starting Member

7 Posts

Posted - 2011-08-10 : 11:19:16

Hi, with this query:

select yr, count(title) from movie
join casting
on (movie.id=casting.movieid)
join actor
on (casting.actorid=actor.id)
where actor.name="John Travolta"
group by yr Asc

How can I display only the record(s) with the most title? I can't use the Max and wrap around count(title). Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-08-10 : 11:24:08

The quickest way would be:
select yr, max(title)
from
(
select yr, count(title) title from movie
join casting
on (movie.id=casting.movieid)
join actor
on (casting.actorid=actor.id)
where actor.name="John Travolta"
group by yr Asc
) a
group by yr

k80sg
Starting Member

7 Posts

Posted - 2011-08-10 : 11:45:22

quote:
Originally posted by RickD

The quickest way would be:
select yr, max(title)
from
(
select yr, count(title) title from movie
join casting
on (movie.id=casting.movieid)
join actor
on (casting.actorid=actor.id)
where actor.name="John Travolta"
group by yr Asc
) a
group by yr

Thanks, it's display the same result but if I omit the 'group by yr' at the end after ') a' it gives me a 'half correct' result because there are actually 2 count(title) of he same value, it returns only 1 with this current query. Anyone can help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources