Select top10 accoding to frequency of occurenece

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 2005 Forums

Transact-SQL (2005)

Select top10 accoding to frequency of occurenece

Author

Topic

last
Starting Member

25 Posts

Posted - 2009-04-07 : 07:23:40

Please help

I currently have a query that returns
Three Columns: Number,Category, Subcategory It's a Simple select Statement

Select NUMBER,CATEGORY,SUBCATEGORY
from Dept

The issue i have is how to make the query return only Top 1o occurences per sub category.The 'Select TOP 1O' query only returns the first ten rows.e.g.
I have a Category called Shoes and the Subcatogeries: Nike 4 coccureneces, Reebok 5 occurences, Addidas 7 occurences,Levis 4. I would like the output to be such.

NUMBER CATEGORY SUBCATEGORY
1 Shoes Addidas
2 Shoes Addidas
3 Shoes Addidas
4 Shoes Addidas
5 Shoes Addidas
6 Shoes Addidas
7 Shoes Addidas
8 Shoes Reebok
9 Shoes Reebok
10 Shoes Reebok

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-07 : 07:27:07

huh that makes no sense?

isn't that just



SELECT TOP 10
    [number]
    , [category]
    , [SubCategory]
FROM
    dept
ORDER BY
    [number]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 07:28:40

[code]SELECT Category,
Items
FROM (
SELECT Category,
Items,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Items DESC) AS recID
FROM (
SELECT Category,
COUNT(*) AS Items
FROM Dept
GROUP BY Category
) AS d
) AS d
WHERE recID <= 10
ORDER BY Category[/code]

E 12°55'05.63"
N 56°04'39.26"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 08:32:24

quote:

Originally posted by Peso

SELECT		Category,
		Items
FROM		(
			SELECT	Category,
				Items,
				ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Items DESC) AS recID
			FROM	(
					SELECT		Category,
							COUNT(*) AS Items
					FROM		Dept
					GROUP BY	Category
				) AS d
		) AS d
WHERE		recID <= 10
ORDER BY	Category

E 12°55'05.63"
N 56°04'39.26"

hi peso,
this will not generate the required output..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:42:27

[code]SELECT Number,
Category,
SubCategory
FROM (
SELECT Number,
Category,
SubCategory,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Number) AS recID
FROM Dept
) AS d
WHERE recID <= 10[/code]

E 12°55'05.63"
N 56°04'39.26"

last
Starting Member

25 Posts

Posted - 2009-04-07 : 08:43:20

Thanks for your replies guys I tried both your queries but they don't produce the result i want .

Maybe my explanation is a bit confusing ,let me try it again.
Currently my Table called Dept has 25 records and has the Columns number(a primary key(int)) Category(varchar) and Subcateogry(varchar).
Currently there is only one category called Shoes but has
various sub Categories .The subcategory itmes with the highest no of records in the table are Reebok(5 records),Nike(4) Addidas(7)
Levi(4). What i wanted to do is make it Select the Top 10 Records depending how many appear in the Subcategory So in this case
my return should return.
NUMBER CATEGORY SUBCATEGORY
1 Shoes Addidas
2 Shoes Addidas
3 Shoes Addidas
4 Shoes Addidas
5 Shoes Addidas
6 Shoes Addidas
7 Shoes Addidas
8 Shoes Reebok
9 Shoes Reebok
10 Shoes Reebok

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:45:18

Try the suggestion made 04/07/2009 : 08:42:27

E 12°55'05.63"
N 56°04'39.26"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 08:46:51

quote:

Originally posted by Peso

SELECT	Number,
	Category,
	SubCategory
FROM	(
		SELECT	Number,
			Category,
			SubCategory,
			ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Number) AS recID
		FROM	Dept
	) AS d
WHERE	recID <= 10

E 12°55'05.63"
N 56°04'39.26"

Hi peso now its giving required op
tanx...

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 08:52:47

hi peso,

can u please give the equivalent query in mssql2000

tanx in advance...

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 08:58:52

This query will give the output. But i think we can optimize it.

with cte as
( select *,row_number() over (partition by Category order by Number desc) as rn from Items ),
cte1 as
(
select *,
case
when ((select sum(Number) from cte where Category = c.Category and rn <= c.rn) < 10) then Number
when (((select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 10) and
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 0)) then
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn))
else 0
end as Total
from cte c
),
cte2 as
(
select Category,SubCategory,1 as ItemId from cte1 where Total > 0
union all
select cte1.Category,cte1.SubCategory,ItemId + 1 from cte1
inner join cte2 on cte1.Category = cte2.Category and cte1.SubCategory = cte2.SubCategory
where ItemId < Total

)
select Category,SubCategory from cte2
order by Category,SubCategory

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:00:58

quote:
Originally posted by soorajtnpki

hi peso,

can u please give the equivalent query in mssql2000

tanx in advance...

Try 1 and 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:04:58

quote:
Originally posted by ddramireddy

This query will give the output. But i think we can optimize it.

with cte as
( select *,row_number() over (partition by Category order by Number desc) as rn from Items ),
cte1 as
(
select *,
case
when ((select sum(Number) from cte where Category = c.Category and rn <= c.rn) < 10) then Number
when (((select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 10) and
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 0)) then
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn))
else 0
end as Total
from cte c
),
cte2 as
(
select Category,SubCategory,1 as ItemId from cte1 where Total > 0
union all
select cte1.Category,cte1.SubCategory,ItemId + 1 from cte1
inner join cte2 on cte1.Category = cte2.Category and cte1.SubCategory = cte2.SubCategory
where ItemId < Total

)
select Category,SubCategory from cte2
order by Category,SubCategory

OP was asking for it's equivalent of SQL Server 2000

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:09:18

Another one posting in wrong forum.

E 12°55'05.63"
N 56°04'39.26"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 09:12:53

hi madhivanan tanx...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:17:10

quote:
Originally posted by soorajtnpki

hi madhivanan tanx...

You are welcome

Madhivanan

Failing to plan is Planning to fail

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 09:19:45

Madhivanan,
The person who asked 2000 version query is not the Thread Starter.
and also one more point to observe is here the user asked the list of repeated entries. suppose addidas shoe has 5 in its count in the table, if it satsifies the conditions, then there should be in 6 entries in output. To Write this in sql2000, its not possible with single query, we need to write a procedure to get this output.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:23:52

quote:
Originally posted by ddramireddy

Madhivanan,
The person who asked 2000 version query is not the Thread Starter.
and also one more point to observe is here the user asked the list of repeated entries. suppose addidas shoe has 5 in its count in the table, if it satsifies the conditions, then there should be in 6 entries in output. To Write this in sql2000, its not possible with single query, we need to write a procedure to get this output.

Yes. I see now that OP is not soorajtnpki. Thanks

Madhivanan

Failing to plan is Planning to fail

last
Starting Member

25 Posts

Posted - 2009-04-07 : 09:30:40

PESO
My Query is

SELECT SUBCATEGORY,Category,
		Items
FROM		(
			SELECT	SUBCATEGORY,Category,
				Items,
				ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Items DESC) AS recID
			FROM	(
					SELECT		SUBCATEGORY,Category,
							COUNT(*) AS Items
					FROM		Dept
					GROUP BY	Category,SUBCATEGORY
				) AS d
		) AS d
WHERE		recID <= 10
ORDER BY	Category

The output is only merely relating the total for each Subcategory
SUBCATEGORY CATEGORY ITEMS
Addidas Shoes 7
Reebok Shoes 5
Nike Shoes 4
Levi Shoes 2

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 09:34:49

Hi Last,

Now I implemented your scenario in my machine. These are
my table script,insert statements, query and output.

create table items
(
Number int,
Category varchar(100),
SubCategory varchar(100)
)

insert into items
select 4,'Shoes','Nike' union all
select 5,'Shoes','Adidas' union all
select 7,'Shoes','Reebok' union all
select 4,'Shoes','Levis' union all
select 6,'Shoes1','Nike' union all
select 3,'Shoes1','Adidas' union all
select 7,'Shoes1','Reebok' union all
select 4,'Shoes1','Levis'

;with cte as
( select *,row_number() over (partition by Category order by Number desc) as rn from Items ),
cte1 as
(
select *,
case
when ((select sum(Number) from cte where Category = c.Category and rn <= c.rn) < 10) then Number
when (((select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 10) and
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn) > 0)) then
((Number + 10) - (select sum(Number) from cte where Category = c.Category and rn <= c.rn))
else 0
end as Total
from cte c
),
cte2 as
(
select Category,SubCategory,1 as ItemId from cte1 where Total > 0
union all
select cte1.Category,cte1.SubCategory,ItemId + 1 from cte1
inner join cte2 on cte1.Category = cte2.Category and cte1.SubCategory = cte2.SubCategory
where ItemId < Total

)
select Category,SubCategory from cte2
order by Category,SubCategory

Output:
Shoes Adidas
Shoes Adidas
Shoes Adidas
Shoes Reebok
Shoes Reebok
Shoes Reebok
Shoes Reebok
Shoes Reebok
Shoes Reebok
Shoes Reebok
Shoes1 Nike
Shoes1 Nike
Shoes1 Nike
Shoes1 Reebok
Shoes1 Reebok
Shoes1 Reebok
Shoes1 Reebok
Shoes1 Reebok
Shoes1 Reebok
Shoes1 Reebok

last
Starting Member

25 Posts

Posted - 2009-04-07 : 09:54:21

Hi ddramireddy
Your solution is almost close to what i am looking for but I noticed with your insert statement you are giivng the number colum the totals for each SubCategory where as Number is actually a Primary key
in my Table a simple Select Number, Category, SubCategory from Dept would return
Number Category SubCategory
1 Shoes Levi
2 Shoes Reebok
3 Shoes Reebok
4 Shoes Nike
5 Shoes Reebok
etc

But this seems to bring me a step closer. I am still struggling with it. Thanks so far all for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 10:01:55

See suggestion made 04/07/2009 : 08:42:27

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources