question

sand143eep avatar image
sand143eep 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
sql-server-2008sql-server-2005sql-server-2008-r2sql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
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!!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
You wouldn't use the UNION in the actual query. That was just used to generate a test example of how to handle the situation. You would just use the SELECT query that I provided (modified if needed).
0 Likes 0 ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.