May 9, 2003 at 4:27 am
I've got a client-variable called client.briefcase with documentID's (for instance 47,36,187)
Now I want to select the actual documents in the order they were put into client briefcase:
SELECT int_DocId, vch_DocTitle
FROM tbl_ah_docs
WHERE int_DocId IN (#client.briefcase#)
ORDER BY ??
How do get the documents in the order 47,36,187?
If I don't give any order by clause, it returns them in ascending order (36,47,187).
Thanks for any insight provided,
Paul
May 9, 2003 at 9:20 am
Could you add an identity field to your table that increments as records are inserted and use it in your order by clause?
Michelle
May 12, 2003 at 4:50 am
You can use an identity field as mentioned or add a date column to your client briefcase - which you could then use for extra functionality like finding 'dead' briefcases or assessing briefcase take-up after a publicity drive.
SQL Server isn't a flat file system so there is no dirty way TIKO to assess the order that rows are added unless you use data like identity fields or date fields.
May 13, 2003 at 2:45 am
Try creating dynamic sql and parse the briefcase and add case statements to the dynamic sql to do the order by.
declare @briefcase varchar(100),@work varchar(100),@this varchar(10),@order varchar(1000),@ct int,@num int
declare @sql nvarchar(4000)
set @briefcase = '47,36,187'
set @work = @briefcase
set @order = ''
set @num = 0
while @work <> ''
begin
set @CT = charindex(',',@work)
if @CT = 0
begin
set @this = @work
set @work = ''
end
else
begin
set @this = left(@work,@ct-1)
set @work = right(@work,len(@work)-@ct)
end
set @num = @num + 1
set @order = @order + 'when int_DocId=' + @this + ' then ' + convert(varchar,@num) + ' '
end
set @sql = 'SELECT int_DocId, vch_DocTitle FROM tbl_ah_docs WHERE int_DocId IN (' + @briefcase + ') ORDER BY (CASE ' + @order + 'END)'
select @sql
NB This will have limitations on variable sizes.
mimorr's solution would cater for longer briefcase.
Edited by - davidburrows on 05/13/2003 02:47:26 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply