asked
Order By Problem
I have table and it has one column named "Name" with data like A1,A10,A11,A2,A22,A23,A3 etc Now when i fetch data by Order by Name it is coming the way A1,A10,A11,A2,A22,A23,A3 but i need it to be A1,A2,A10,A11,A22,A23 Please help.I need it urgently Thanks & Regards Neel
Hang on. You say in your comment in response to @mjharper's proposed answer that your data contains data like: A1 B1 A2 Now, do you want the sorting to be done just on the number, or do you need the sort also to take into account the characters? In other words, does B1 come before or after A2?
The site works on voting. For all helpful answers please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution you can indicate that by clicking on the check mark next to that answer.
You need to change the order by so that it orders by the integer part of your Name column. If there is always just one preceding letter you can use the RIGHT function to get the integer part and then cast it as an INT. So your order by would become: ORDER BY CAST(RIGHT(name, LEN(name) -1) AS INT) Normally I would have an OrderId column in the table that contains the Name so that I can select and order by that column rather than having to extract parts of the Name column and cast them. Hope that helps.
You need to find a way to extract the integer part of the name field - and then replace the "RIGHT(name, LEN(name) -1)" with that. It's hard to know the best way to extract the integer part without seeing your data - but one possible way is outlined here:
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/ If you created the function shown in that article your new ORDER BY clause would be: ORDER BY CAST(dbo.udf_GetNumeric(Name) as INT)
Thanks for your reply. i am getting an error Conversion failed when converting the nvarchar value ' XXX' to data type int. i have tried changing INT with Varchar, it is giving wrong answer. My Name column in varchar. and i have a column like OrderId , but it is not in the order and it is not serving the purpose. My table looks like ORDERID NAME 1 A1 2 A10 3 B1 4 A15 5 B2 6 A11 7 A2 8 A12
It sounds like there are some instances where the RIGHT function is getting more than just the integer part of the 'name' column. That could happen if you had something like AB123 because then the RIGHT function would extract B123. (as we're telling it to get everything except 1 charater) Can you add RIGHT(name, LEN(name) -1) to the SELECT - then look for a row that doesn't end up with just a number in that column.
Hers's an alternate answer as you need to sort by both the alpha and numeric parts of the string. First I create a # table and add some test data to it - for your example just replace references to #DemoData with your table name. The parts of the query getting the alpha and numeric parts assumes you always have alpha part first and then the numeric part. (You don't need the extra columns in the SELECT - I've just left them in so you can see what you're going to end up ordering by.) CREATE TABLE #DemoData ( id INT IDENTITY(1, 1) , name VARCHAR(10) ) INSERT INTO #DemoData( name ) VALUES ( 'A1' ) INSERT INTO #DemoData( name ) VALUES ( 'B1' ) INSERT INTO #DemoData( name ) VALUES ( 'E1' ) INSERT INTO #DemoData( name ) VALUES ( 'A10' ) INSERT INTO #DemoData( name ) VALUES ( 'A3' ) INSERT INTO #DemoData( name ) VALUES ( 'AB3' ) SELECT * , LEFT(name, PATINDEX('%[0-9]%', name) - 1) AS AlphaPart , RIGHT(name, LEN(name) - PATINDEX('%[0-9]%', name) + 1) AS NumericPart FROM #DemoData ORDER BY LEFT(name, PATINDEX('%[0-9]%', name) - 1) , CAST(RIGHT(name, LEN(name) - PATINDEX('%[0-9]%', name) + 1) AS INT)
It looks like someone has tried to create a "smart key" by combining some kind of code with a number. "smart keys" are really bad for the database design as you end up with "non searchable" arguments like using functions in where clauses and/or order by. The worst case of a smart key is when there are logic tied to a specific relative position of the data. I would probably split the column in two in the table, either by persisted calculated columns or real columns, to be able to create index on the columns. Index on columns that are part of the order by clause may reduce the need of an expensive sort operation in the execution plan
20 People are following this question.
