in sql statement somethin worng

Question

hai firends... i  have a small problem...my case is when i'm pulling the particular records from database  i write the code like this...my problem is the records are not displaying as my wish
  they are binding in alphabetical order in my datalist..i want the records as per the. below identitiesthat means id the recor id is 166 then that record must be in first ..can any one help ....


  con.Open();


            string str = "select * from Candidate where id in(166,175,85,91,155,14,276,5,54,43,314,149,137)";



            SqlCommand cmd = new SqlCommand(str, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);



Thanks and Regards

Answer 1

Assuming yout Table1 will have all working day timings(except for leaves) and Table2 will have Leaves information Below TSQL can give you the required result.
SELECT * INTO #Temp
FROM
(
SELECT
[empid]
,[attdate] AS [Date]
,CAST(DAY([attdate]) AS VARCHAR(2))+ '(' +CAST([attdate] AS VARCHAR(25))+')' AS [Day]
,'Status' = CASE WHEN [ workedhours] > 8 THEN 'P' ELSE 'AB' END
FROM [Table1]
UNION
SELECT
[empid]
,[leavedate] AS [Date]
,CAST(DAY([leavedate]) AS VARCHAR(2))+ '(' +CAST([leavedate] AS VARCHAR(25))+')' AS [Day]
,'Status' = 'L'
FROM [Table2]
) AS [Result]

SELECT DISTINCT [Date],[Day] INTO #Temp2 FROM #Temp ORDER BY [Date]

DECLARE @ColumnNames NVarchar(MAX)
DECLARE @SqlQuery NVARCHAR(max)

SELECT @ColumnNames=STUFF((
select ',['+ [Day] +']'
from #Temp2
FOR XML PATH('')
)
,1,1,'')

SET @SqlQuery = N'SELECT [empID],' + @ColumnNames +' FROM (Select [EmpId],[Day],[Status] From #Temp) P Pivot (MAX([Status]) For [Day] in (' +@ColumnNames+'))AS PivotTable;'

EXEC Sp_executeSql @SqlQuery