top 10 query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

top 10 query

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-07-19 : 14:58:54

I have a table:

daily_linkset_occ

ls_name char(12),
year_month char (6),
count int

ex.
ls0001,200605,1000
ls0002,200605,2000

where there is actually a reocrd for each linkset for each day of the month. I would like to be able to create a view where I would see only 10 records per linkset/year_month, the top ten counts for each month. I'm not that good with joins. Can you help..

RLiss

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-19 : 16:19:46

U don't need JOIN if u r using this table only.
U may use Group By & Top 10

Also refer the Point 2 in
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Srinika

Rliss
Starting Member

31 Posts

Posted - 2006-07-20 : 11:52:35

I could not get it to work using 'group by' and 'top 10'. However, the link provided was very helpful.

Thanks.

RLiss

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 13:02:01

Maybe try something like this:

create table #daily_linkset_occ
(
ls_name char(12),
year_month char (6),
count int
)

declare @year_month char(12)
declare c1 cursor for
select distinct year_month from daily_linkset_occ

open c1

fetch c1 into @year_month
WHILE (@@FETCH_STATUS = 0)
begin
insert into #daily_linkset_occ
select top 10 * from daily_linkset_occ
where year_month = @year_month
order by year_month, count desc
fetch c1 into @year_month
end
close c1
deallocate c1

select * from #daily_linkset_occ

drop table #daily_linkset_occ
go

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 13:14:39

To create a view that returns the desired results, use a user defined function like this:

IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_MyFunction')
DROP FUNCTION fn_MyFunction
GO

CREATE FUNCTION fn_MyFunction()
RETURNS @mytable TABLE
( ls_name char(12),
year_month char (6),
count int
)
AS
BEGIN
declare @year_month char(12)
declare c1 cursor for
select distinct year_month from daily_linkset_occ

open c1

fetch c1 into @year_month
WHILE (@@FETCH_STATUS = 0)
begin
insert into @mytable
select top 10 * from daily_linkset_occ
where year_month = @year_month
order by year_month, count desc
fetch c1 into @year_month
end
close c1
deallocate c1
RETURN
END
GO

CREATE VIEW Myview as
SELECT * FROM fn_MyFunction()

GO

select * from Myview

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources