asked
order by is not working
Hello Team, we have a table where files column like FR1 ,FR10 ,FR2 ,FR3 ,FR4 ,FR5 ,FR6 ,FR7 ,FR8 ,FR9 when we are using order by to the column of this file to get the F10 value below F9 is not working its occupying the second column, the testers are asking to get the data in the following form FR1 ,FR2 ,FR3 ,FR4 ,FR5 ,FR6 ,FR7 ,FR8 ,FR9 ,F10 ,F11 like that. please suggest how to achieve this. currently i am using the query SELECT distinct file_id, file_type, file_inc_01, file_inc_02, file_week_0_10, file_week_10_25, file_week_25_45, FROM [dbo].co_file_tb order by file_id
I'm assuming that the column type is string, IE: varchar/nvarchar. If this is the case, the ORDER BY is working as it should since it's ordering the strings in appropriate order. FR10 will come before FR2 since the 1 in 'FR10' is ordered prior to '2'. You can either adjust the query to extract the number from the file name and then order by that or you can add in another column that defines the order. CREATE TABLE #OrderTest (file_name VARCHAR(15)) GO INSERT #OrderTest SELECT 'FR1' UNION SELECT 'FR2' UNION SELECT 'FR3' UNION SELECT 'FR4' UNION SELECT 'FR5' UNION SELECT 'FR6' UNION SELECT 'FR7' UNION SELECT 'FR8' UNION SELECT 'FR9' UNION SELECT 'FR10' UNION SELECT 'FR11' GO SELECT CAST(RIGHT([file_name],LEN(file_name)-2) AS INT) AS 'OrderBY',* FROM #OrderTest ORDER BY OrderBy Note: The query above assumes that the file name format of "FRXXXX" doesn't change within the table. If the file name structure changes, you will probably have to adjust the query to handle it, depending on what it changes to. Hope that helps!!
but it can go to FR1 to FR10000 or to infinite as files will be added from now, then it is difficult to add union for every file. please suggest here.
Thanks for the clarification i changed the query like SELECT distinct CAST(RIGHT([file_id],LEN([file_id])-2) AS INT),replace(file_id,'','FR') AS file_id, file_type, file_inc_01, file_inc_02, file_week_0_10, file_week_10_25, file_week_25_45, file_week_45_above FROM co_file_tb then i got the result as expected. but there is one more column with no column name in front. why this column appeared as a new one. i wanted only the selected columns not the extra new column. please suggest with your answer.
hi john, please help if there is any possibility to get the result in one column for the following query select CAST(RIGHT([file_id],LEN([file_id])-2) AS INT),replace(file_id,'','FR') AS file_id from co_file_tb
18 People are following this question.
