Unusual ordering of values in SQL

[user1980]

Hello there,


I have checkbox list which has the following values and text

<asp:ListItem Text="a" Value="5" />
<asp:ListItem Text="b" Value="3" />
<asp:ListItem Text="c" Value="1" />
<asp:ListItem Text="d" Value="4" />
<asp:ListItem Text="e" Value="2" />
<asp:ListItem Text="f" Value="6" />


This information is stored in the database as
Column1
5,3,2,6,
3,1,4,
5,1,
1,4,2,

depending on the selections made by user.
I have a split function which would split the column values,

RowNo      Value
1           5
2           3
3           2
4           6

Now the problem is, I have to put these values into another bigger table, in the order
1,4,5,3,2,6

if the user did not select say,1 and 3 then the table must take the order 4,5,2,6
if the user did not select say,1,4 and 3 then the table must take the order 5,2,6

So in the above case, for the first input, the order should be 5,3,2,6
and for the second one 1,4,3.

so is there way to achieve this??can somebody please help me to how to proceed on this.

[ck9663]

Can you replace the value on your listbox to reflect the sort order?

Happy Coding!!!

~~ CK

[user1980]

Nope they are primary keys in another table and I would need to store only the value so that the application picks the text value from the value.

[ck9663]

If you can control that table, you might want to add a field called SORTORDER that you can grab just to get the order of this values.

If not, you will need a separate table for it.

Good Luck!!!

~~ CK

[user1980]

I would not be able to modify the table with the primary keys, as it is supported by a vendor and any changes to it would violate the terms with the vendor. can you please give me an idea on how to proceed on creating a new table for these values with a sort order...can I use something in my split function and arrange them in that function?

[nbiswas]

Solution to Unusual ordering of values in SQL

Step 1:
Create a table like

Create  table tblFixedRecords(Record int)
insert into tblFixedRecords select 1 union all select 4 union all select 5 union all select 3 union all select 2 union all select 6

Step 2:

Use the split function and store the records(which the user chooses from asp.net application) into a temporary table(it should have only one column say VariableRecord of type int) like

Insert into #tempVariableRecord(VariableRecord)
Select TheSplitFunction(Values1,Values2, etc...)

Say the user choosen 1,4,5.

So in this case the #tempVariableRe cord will have the values as

1
4
5

Step 3:

Perform any one of the below queries

a)

Select Record from tblFixedRecords 
    EXCEPT
   Select VariableRecord from #tempVariableRecord

b)

Select Record from tblFixedRecords 
    Where Record NOT IN (Select VariableRecord from #tempVariableRecord)

c)

Select Record from tblFixedRecords  t1
    Left Join
  Select VariableRecord from #tempVariableRecord  t2
  ON t1.Record  = t2.VariableRecord
Where t1.Record = Null

The output in this case will be

3
2
6


N.B.~ If you need to join the record sets then use For XML Path among other options

e.g.(Using the first query)

SELECT 
    DelimitedRecord = left(DelimitedRecord,len(DelimitedRecord) -1 )
FROM 
    ( 
    SELECT  
       ',' +  CAST(Record AS varchar(8000)) 
    FROM  
        ( Select Record from tblFixedRecords  EXCEPT 
        Select VariableRecord from #tempVariableRecord ) T
    FOR XML PATH ('') 
    )X(DelimitedRecord)

Output:

3,2,6

Hope this will help you