SQL - a way to force next record automatically? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

SQL - a way to force next record automatically?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-06-29 : 07:27:48

Nicole writes "Hi,

I'm at a loss as to how to solve this problem, and I found your site - I'm hoping this will be an easy one for you! Please let me know if you need more information, and I'll provide it.

Here's the scenario:
-I have a list of music shows, each show containing a tracklist of up to 10 songs.
-Each music show is scheduled according to a calendar in the database.

What I want to do is query the database when the next show needs to be retrieved (after the current show's last song is done and refreshes, or the user hits the next button on the last song).

I have sucessfully created a query to list all the songs in the desired order for the realplayer, but I am having trouble with the query that finds the proper show after the preceding show ends.

Here is the query:

(#DJ_Show_Id# #SchedDateId# are dynamic variables sent from the page)

SELECT TOP 1 DJ_Show_Id AS Id, SchedDateId
FROM Show_Feature SF
WHERE DJ_Show_Id <> #DJ_Show_Id#
AND SF.SchedDateId <= '#SchedDateId#'
ORDER BY SchedDateId DESC, DJ_Show_Id ASC

Here is the correct data order (id is the show ID and SCHED is the schedule ID - 431 is most recent date):

ID:1 SCHED:431
ID:9 SCHED:431
ID:16 SCHED:431
ID:17 SCHED:431
ID:9 SCHED:398
ID:16 SCHED:398
ID:1 SCHED:303
ID:2 SCHED:303

With the above query, the results flip from sched 431-show 1 to sched 431-show 9 in a continuous loop. It should move through 1,9,16,17,9,16,1,2 (according to schedule date).

Is there any way that I can force the query to move to the next record automatically? I'm new to SQL server, and I'm not sure if there are any specific commands that I can use.

Does this make sense? If not, let me know and I'll try to explain it differently.

Thanks in advance for your help,
Nicole Goodman"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources