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!!