Returning records in alternating order

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)

Returning records in alternating order

Author

Topic

LogicalOnline
Starting Member

2 Posts

Posted - 2004-04-12 : 22:23:00

Hello,

I’m sure some smart person out there can help me with this query. Basically what I need is the ability to call a stored procedure and pass it a priority level and the number of records to return from the Transactions table where the status is 0. Sounds simple enough but there’s a catch. I need it to return records in a specific alternating order.

CREATE TABLE [dbo].[Transactions] (
[TransID] [uniqueidentifier] NULL ,
[BatchID] [int] NULL ,
[Priority] [int] NULL ,
[Status] [int] NULL
) ON [PRIMARY]

TransID,BatchID,Priority,Status
{20CE6CAA-CB8F-4872-9D77-5E3216F6457B},,1,0
{775F43B6-3BA4-4DE1-BFB0-107309F7B985},,1,0
{606CDFC7-7B10-4E9C-B62F-D79567900785},,1,0
{3156BAB3-1F1B-4F81-BFF0-EA3DF1331AAC},21000,1,0
{3201B575-4225-432F-894C-91547661F044},21000,1,0
{F2CFAD36-868C-4A4D-A8E8-66942CAC3CBD},21000,1,0
{3EF74481-0A39-4526-B9F4-C3ABAC2E5258},21000,1,0
{7EB09EC2-9F7A-43DE-B168-A8A9529E1ECE},22000,4,0
{DAF8FCE0-AD54-44DD-8C0E-D61921CAF6CE},22000,4,0
{E48AD817-1BB4-4B89-AB37-E4ED3030E0C8},22000,4,0
{C8F04985-892F-491A-BEF0-AED118288BF3},22000,4,0
{D497C9A4-B2B2-49A9-8004-964A0526986A},23000,6,0
{319596A5-C878-4D93-9064-455D41F74643},23000,6,0
{522B2EBD-C80D-468A-9494-59BA3A8B24BB},23000,6,0
{4583C87B-7CC1-4DFE-9C98-1DBF0BFBD7F5},24000,6,0
{27B611F5-2D47-41EA-89C5-99B1B807C9FC},24000,6,0
{5657F76F-36C3-457E-9D70-252FB28EE08C},24000,6,0
{A71A4A12-84C0-4007-AA89-9E616FFBB8B4},25000,6,0
{1053FD04-F3C7-4B49-B31B-EC6A39A46154},25000,6,0
{27B787DF-6C2C-43F3-BD2E-C1710D8D56B6},25000,6,0

For example:

EXEC spMagicQuery 6, 5

Would return:

{D497C9A4-B2B2-49A9-8004-964A0526986A},23000
{4583C87B-7CC1-4DFE-9C98-1DBF0BFBD7F5},24000
{A71A4A12-84C0-4007-AA89-9E616FFBB8B4},25000
{319596A5-C878-4D93-9064-455D41F74643},23000
{27B611F5-2D47-41EA-89C5-99B1B807C9FC},24000

It would return 5 records with priority level 6 alternating by order of BatchID and update the Transaction table records return with status = 1. The fun part... If I called it again, I need it to return the next set starting where it left off. In this case 25000:

{1053FD04-F3C7-4B49-B31B-EC6A39A46154},25000
{522B2EBD-C80D-468A-9494-59BA3A8B24BB},23000
{5657F76F-36C3-457E-9D70-252FB28EE08C},24000
{27B787DF-6C2C-43F3-BD2E-C1710D8D56B6},25000

Since there are only 4 records left, it would just return those 4.

If at any time there are NULL BatchID records, those records should always be returned before any others i.e.:

EXEC spMagicQuery 1,4

{20CE6CAA-CB8F-4872-9D77-5E3216F6457B},
{775F43B6-3BA4-4DE1-BFB0-107309F7B985},
{606CDFC7-7B10-4E9C-B62F-D79567900785},
{3156BAB3-1F1B-4F81-BFF0-EA3DF1331AAC},21000

Any thoughts?

kroky
Starting Member

14 Posts

Posted - 2004-04-14 : 07:10:06

Go and see your topic in T-SQL folder.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources