How to select X number of rows based on a SUM

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)

How to select X number of rows based on a SUM

Author

Topic

k420
Starting Member

32 Posts

Posted - 2008-10-17 : 10:59:06

Hi All,

Hope you can help me with a problem that I have no clue how to solve without using a cursor. I can't even come up with a could title for the topic ;-)

I have a table of items that are to be played in a media player that looks something like this:

CREATE TABLE [tblMediaItem] (
	[pinMediaItem] [int] IDENTITY (1, 1) NOT NULL ,
	[vcPathAndFilename] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
	[flDisplayDurationSecs] [decimal](18, 0) NOT NULL ,
	CONSTRAINT [PK_tblMediaItem] PRIMARY KEY  CLUSTERED 
	(
		[pinMediaItem]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

The data could be something like this:

INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path1', 15)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path2', 30)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path3', 15)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path4', 15)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path5', 15)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path6', 15)
INSERT INTO tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path7', 15)

For whatever reason I only want to play X seconds worth of content at any one time. So If I only want to get the first 60 seconds worth of content I could loop through the items using a cursor and put each row into a temporary table variable until I get 60 seconds worth with a simple IF statement in the loop.

In reality the table will hold a lot more than just a few rows and I will maintain a record of where I was up to and things like that so that I don't play the same items over and over. So, is it possible to do this without using a cursor?

Thanks for your time

Keith

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-17 : 11:27:08

Do you want exactly 60 seconds?...what happens if you are on 59 seconds and the choice of the next 2 is either 2 seconds or 20 seconds....which do you want?

k420
Starting Member

32 Posts

Posted - 2008-10-17 : 12:15:23

60 seconds would be the maximum so if you are on 59 seconds and the next item took it over then the next item shouldn't be included.

The 60 seconds is just an arbitrary number. The maximum could be any number of seconds but the main thing is to never go over the maximum and if that means that its short by X seconds then that's ok.

Cheers

Keith

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 16:04:45

[code]declare @tblMediaItem TABLE (
[pinMediaItem] [int] IDENTITY (1, 1) NOT NULL ,
[vcPathAndFilename] [varchar] (100) ,
[flDisplayDurationSecs] [decimal](18, 0) NOT NULL

)

INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path1', 15)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path2', 30)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path3', 15)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path4', 15)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path5', 15)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path6', 15)
INSERT INTO @tblMediaItem (vcPathAndFilename, flDisplayDurationSecs) VALUES ('path7', 15)

select * from (
select z.*, (
select sum(a.flDisplayDurationSecs) from @tblMediaItem a , @tblMediaItem b
where a.pinMediaItem <= b.pinMediaItem
and b.pinMediaItem = z.pinmediaitem
) as accu
from @tblMediaItem z
)t
where accu <= 60
[/code]

k420
Starting Member

32 Posts

Posted - 2008-10-18 : 06:42:24

Thanks Hanbingl. I've had a quick play and that looks like it will do the trick nicely. I'll have a proper go at integrating it into my code on Monday when I'm back in work.

Cheers

Keith

k420
Starting Member

32 Posts

Posted - 2008-10-20 : 05:17:51

I've had more of a play with the SQL and the bit that I didn't spot on Saturday was that the inner part of the SQL selects all items in the media item table when it's doing the accumulation.

So, if I have 1000 items in the table then all 1000 get selected in the inner part. If I were using a cursor, I would only select the number of items required to fill the amount of time I want to work with which could easily only be 4 items.

I'm wondering, is this one of those cases where it would actually be quicker / more efficient to use a cursor?

Cheers

Keith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources