pubs: TOP 2 publishers for each Type

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

SQL Server 2000 Forums

Transact-SQL (2000)

pubs: TOP 2 publishers for each Type

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-11-05 : 14:59:25

Hi all,

I can't figure out how to use TOP within groups.

Take for example the table "titles" in pubs. How can I show the TOP 2 publishers (based on the count of titles in that type) in each "type"?

Examples of "type" are:
- business
- mod_cook
- popular_comp
- psychology
- trad_cook

I want to show:

|--- type ---- pub_id ----- numtitles -----|

Thanks in advance.

Tim

izaltsman
A custom title

1139 Posts

Posted - 2002-11-05 : 17:05:22

I'm thinking something like this might be deadly slow, but will work.


select t.type, t.pub_id, count(*) as numtitles
from titles t 
where pub_id in 
    (select top 2 pub_id
    from titles ti where  ti.type =t.type
    group by pub_id
    order by count(*) desc)
group by t.type, t.pub_id
order by t.type, t.pub_id

Edited by - izaltsman on 11/05/2002 17:11:21

TimSinnott
Starting Member

48 Posts

Posted - 2002-11-05 : 17:24:05

It worked perfectly, Ilya. :D

I simply could not figure that out on my own. I'm still trying to soak it in. :p

Many thanks.

Tim

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-11-05 : 19:13:32

I get this when I run it :

type pub_id numtitles
------------ ------ -----------
business 0736 1
business 1389 3
mod_cook 0877 2
popular_comp 1389 3
psychology 0736 4
psychology 0877 1
trad_cook 0877 3
UNDECIDED 0877 1

(8 row(s) affected)

Is it what you get too? If so, I can get the same results by :

select type, pub_id, count(*)
from titles
group by type,pub_id

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 19:17:17

I think that's because of the amount of data in the pubs tables, if there were more rows the two queries would indeed return different results.

I'm not 100% sure this is what you're looking for:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21161

Joe Celko posted some code that might do the trick.

TimSinnott
Starting Member

48 Posts

Posted - 2002-11-05 : 20:01:54

Thanks, ConfusedOfLife. Yes, Rob is right in that if there were more records in the titles table, the results would be different. I should have used a larger table to illustrate my question. The main objective was how to limit the result to only the TOP X publishers for each category (type).

Thanks for the link, Rob. I didn't fully understand that solution or its application in my situation. However, Ilya's example pretty much solves my problem, though, so far.

Tim

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-11-06 : 13:56:09

Yeah, you're right, I was so tired last night that I didn't check the pubs, but when I worked on that today I just got the same results.

PS: Correlated subqueries are just cool and the sql language is really beautiful, you should look at it from a different view and it's not like any other programming language ( those working with ifs and fors!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources