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

Transact-SQL (2000)

Returning records in alternating order

Author

Topic

LogicalOnline
Starting Member

2 Posts

Posted - 2004-04-12 : 12:29:21

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 : 04:49:35

Hi
I wont solve your problem but i'll show you what i made for the problem:
you have a table ids with only column id and suppose you have

select id from ids:

1
1
2
2
3
3
4

so you want to select it that way

1234123

i'm almost sure there is a way to solve this problem by useing a cursor but i dont like them so i'll give you my sollution by useing a temp talbe:

create table #tmp (MyID int identity(1,1), id int)
declare @i int
declare @stop int
set @stop = 1
set @i = 0

while @stop <> 0
begin
insert into #tmp (id)
select ID from ids
group by id
having count(*) > @i

set @stop = @@rowcount
set @i = @i + 1

end

select id from #tmp order by MyID

drop table #tmp

by executing this you will receive the dezierd result so you have to implement this behaviour in your tables...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources