By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Unusual ordering of values in SQL

100+
P: 112
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,

Expand|Select|Wrap|Line Numbers
  1. RowNo      Value
  2. 1           5
  3. 2           3
  4. 3           2
  5. 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.
Jan 27 '10 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Can you replace the value on your listbox to reflect the sort order?

Happy Coding!!!

~~ CK
Jan 27 '10 #2

100+
P: 112
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.
Jan 27 '10 #3

ck9663
Expert 2.5K+
P: 2,878
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
Jan 27 '10 #4

100+
P: 112
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?
Jan 27 '10 #5

nbiswas
100+
P: 149
Step 1:
Create a table like

Expand|Select|Wrap|Line Numbers
  1. Create  table tblFixedRecords(Record int)
  2. 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


Expand|Select|Wrap|Line Numbers
  1. Insert into #tempVariableRecord(VariableRecord)
  2. Select TheSplitFunction(Values1,Values2, etc...)
Say the user choosen 1,4,5.

So in this case the #tempVariableRecord will have the values as

1
4
5

Step 3:

Perform any one of the below queries

a)
Expand|Select|Wrap|Line Numbers
  1. Select Record from tblFixedRecords 
  2.     EXCEPT
  3.    Select VariableRecord from #tempVariableRecord 
b)
Expand|Select|Wrap|Line Numbers
  1. Select Record from tblFixedRecords 
  2.     Where Record NOT IN (Select VariableRecord from #tempVariableRecord)
c)
Expand|Select|Wrap|Line Numbers
  1. Select Record from tblFixedRecords  t1
  2.     Left Join
  3.   Select VariableRecord from #tempVariableRecord  t2
  4.   ON t1.Record  = t2.VariableRecord
  5. 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)

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DelimitedRecord = left(DelimitedRecord,len(DelimitedRecord) -1 )
  3. FROM 
  4.     ( 
  5.     SELECT  
  6.        ',' +  CAST(Record AS varchar(8000)) 
  7.     FROM  
  8.         ( Select Record from tblFixedRecords  EXCEPT 
  9.         Select VariableRecord from #tempVariableRecord ) T
  10.     FOR XML PATH ('') 
  11.     )X(DelimitedRecord) 
Output:

Expand|Select|Wrap|Line Numbers
  1. 3,2,6
Hope this will help you
Mar 24 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.