How to list the records based on my given order

Hi,

How you decide the order of 2,15,4,1,45?

So you must have some other field to decide the order by. So place that field in the order by clause.

or


query by each ids and get the row and but it in collection classes or data classes, that will give you the right output.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Pl try using Temp table

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Hi PMM and Nk,

Thanks for your response however I do not have any other column to order on and I do not want to use temp table (in case of temp table, I will need to get each record and add into temp table that is not good).

I just need to show the records I want to show in the order I am feeding in why should db given me the ordered record if I am not asking for that?

Poster, if this helps please login to Mark As Answer. | Alert Moderator

hi,

How you are passing the ids? comma separated? Are you using XML type as argument?

Is it a procedure or what? We need a clear picture. If you are using IN clause then you cannot get the desired ordered result.

try with @temp table variable. I think no other way.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Hi
I tried using Temp table only, it works.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Yes, I am using Xml data type and passing the data as xml string that my stored procedure is taking care of executing it. But I am not getting the output in the order I am sending.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Hi

The following are the reasons...

You may have created CLUSTERED INDEX on "Ids" Column as given below...

1. You may have created CLUSTERED PRIMARY KEY on "Ids" Column.
(or)
2. You may have created CLUSTERED INDEX without Parimary Key on "Ids" Column.

So, When you create a CLUSTERED INDEX on column, The actual data itself sorted on Leaf level itself...

If you need the record sequence as Inserted, You should not be created CLUSTERED INDEX on that column....

But... A table should have CLUSTERED Primary Key... So, Try to create CLUSTERED INDEX on some other column otherthan the "Ids" column....

Note: If you have created a PRIMARY KEY with Non-Clustered on "Ids" columns, It should nor be aa Problem... Because, Non-Clustered index not physically sorted the actual data....

In your case, The column is Auto generated.... So, You have no direct way to get the record in your specific order...

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Thank you very much Pandians, I was expecting your reply as I have read some of your responses for Sql Server questions here.

Could you please write a robust and efficient code snippet that will insert the data into the temp table in the order I want. I do not want to use temp table so the CTE will work here? An article from you on this topic will really help to understand why, when etc and the better alternate of temp table or temp variable (if not better way to deal with them).

Thank you

Poster, if this helps please login to Mark As Answer. | Alert Moderator