How to do an Order by in my Select statement to get the correct sequence order

Good Day,

I am struggling to find a way to properly do the following:

In SqlServer I am pulling a dataset. In this dataset I want to be able to sequence the alphanumeric string in the following way.

 Select Distinct  From Table
  where col1 = 10 and col2 <> '-1'
Order by ????????

Open in new window


Ex.

From DB        Need order by like this
1		        1
10(a)		2
11(a)		3
12(a)		4
13(a)		5
14(a)		6
15(a)		7
15(b)		7(a)
15(c)		7(b)
16(a)		8
16(b)		8(a)
16(c)		8(b)
17(a)		9
17(b)		9(a)
17(c)		10
18(a)		10(a)
18(b)		11(a)
18(c)		12(a)
19(a)		13(a)
19(b)		14(a)
2		        15(a)
20(a)		15(b)
20(b)		15(c)
21		       16(a)
22		       16(b)
23		16(c)
24		17(a)
25		17(b)
26		17(c)
27		18(a)
28		18(b)
29		18(c)
3		19(a)
30		19(b)
31		20(a)
32		20(b)
33		21
34		22
35(a)		23
35(b)		24
35(c)		25
35(d)		26
35(e)		27
35(f)		28
36(a)		29
36(b)		30
36(c)		31
36(d)		32
36(e)		33
36(f)		34
37(a)		35(a)
38(a)		35(b)
39		        35(c)
4		       35(d)
40(a)		35(e)
41(a)		35(f)
42(a)		36(a)
42(b)		36(b)
42(c)		36(c)
43(a)		36(d)
43(b)		36(e)
43(c)		36(f)
44		37(a)
5		38(a)
6		39
7(a)		40(a)
7(b)		41(a)
8(a)		42(a)
8(b)		42(b)
9(a)		42(c)
		43(a)
		43(b)
		43(c)
		44

Open in new window

Mario RichardsonHealthcare AnalystAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
select col1
from table1
outer apply (
  select cast(replace(left(col1,len(col1)-charindex('(',col1)),'(','') as int) x
  ) oa
order by oa.x, col1

Open in new window

see http://sqlfiddle.com/#!18/41e0b/1
0
 
Darrell PorterEnterprise Business Process ArchitectCommented:
Have you tried

ORDER BY dbo.fn_CreateAlphanumericSortValue(Distinct)

Open in new window


where

CREATE FUNCTION [dbo].[fn_CreateAlphanumericSortValue]
(
	@ItemToSort varchar(200),
	@SortNumericsFirst bit = 1
)
RETURNS varchar(801)
WITH SCHEMABINDING
AS
	--==========================================================================================
	-- This function takes an alphanumeric string and encodes it so that it can be properly sorted
	--    against other alphanumeric strings
	-- The encoding will insert a three digit string before each numeric portion of the item to sort
	--    The three digits represent the number of digits in the numeric portion that it will precede (zero-padded)
	-- The encoding will also account for leading zeros in each numeric portion by adding a three digit
	--    string at the end of the item to sort, for each numeric portion.  Those three digits will
	--    represent the number of leading zeros in the numeric portion (zero-padded)
	-- Examples:
	-- ABC =	  ABC
	-- ABC1 =     ABC0011 000
	-- ABC1ABC1 = ABC0011ABC0011 000000
	-- ABC12    = ABC00212 000
	-- ABC012   = ABC00212 001
	--
	-- Worst case notes:
	--	The max count of leading zeros is -- all zeros = 200
	--  The max count of numbers -- all numbers = 200
	--  A single space separates the leading zero string from the rest
	--	Leading zeros get trimmed
	--  Each number portion gets 3 new characters in front (and 3 new characters at the end)
	--			1 leading zero nets 2 characters
	--			2 leading zeros net 1 character
	--			3 leading zeros net 0 characters
	--			>3 lose characters
	--  All characters just spits out the same string
	--  So, no leading zeros, but with numbers, adds the most characters
	--	So, the most number-sets = the most characters -- which is every other is a number
	--			= 100 numbers & 100 alphas
	--			= 100 * 3 characters each in front + 100 * 3 characters each at then + 1 space
	--			= 801 characters
	-- FYI. This function is specifically being used for ... (some domain specific stuff)... sorting at this time.
	--    As such, everything has been set up for that length, which is 200 characters
	--    A change in that length could require numerous changes to to code below -- be careful.
	--		(If you can have > 999 numbers/zeros to count)
	--==========================================================================================
BEGIN
	declare @WorkingItem varchar(200) = @ItemToSort
	declare @DigitCount int = 0
	declare @LeadingZeroCount int = 0
	declare @CurrentNumber varchar(200) = ''
	declare @Leftmost varchar(1) = ''
	declare @LeadingZeroString varchar(300) = ''

	--==========================================================================================
	-- With 200 character input, the worst case output should be 801 characters
	--==========================================================================================
	declare @SortValue varchar(801) = ''	

	--==========================================================================================
	-- We will work thru the input string one character at a time
	--==========================================================================================
	declare @FirstIsCharacter bit = 0
	if (isnumeric(left(@WorkingItem, 1)) = 0)
		select @FirstIsCharacter = 1

	while (len(@WorkingItem) > 0)
	begin
		select @Leftmost = left(@WorkingItem, 1)

		--==========================================================================================
		-- Is the first character a number?
		--==========================================================================================
		if (isnumeric(@Leftmost) = 1 and @Leftmost != '-')
		begin
			while (isnumeric(@Leftmost) = 1 and @Leftmost != '-')
			begin
				--==========================================================================================
				-- Parse out all of the consecutive digits to get the current number
				--==========================================================================================
				if (@Leftmost = '0' and @DigitCount = 0)
				begin
					--==========================================================================================
					-- Leading zero -- just count how many we have in this set of digits
					--    We'll add the string for it to the end of our output below
					--==========================================================================================
					select @LeadingZeroCount = @LeadingZeroCount + 1
				end
				else
				begin
					--==========================================================================================
					-- Not a leading zero, so increment the digit count, and remember the current number value
					--==========================================================================================
					select @DigitCount = @DigitCount + 1
					select @CurrentNumber = @CurrentNumber + @Leftmost
				end

				--==========================================================================================
				-- Trim off the character we just checked, get the next character to check and continue the inner loop
				--==========================================================================================
				select @WorkingItem = substring(@WorkingItem, 2, 200)
				select @Leftmost = left(@WorkingItem, 1)
			end -- while (isnumeric(@Leftmost) = 1)

			--==========================================================================================
			-- We now have the current number from our input string
			--    Add the current number's leading zero string to the entire leading zero string, zero-padded
			--==========================================================================================
			if (@LeadingZeroCount < 10)
				select @LeadingZeroString = @LeadingZeroString + '00' + cast(@LeadingZeroCount as varchar)
			else if (@LeadingZeroCount < 100)
				select @LeadingZeroString = @LeadingZeroString + '0' + cast(@LeadingZeroCount as varchar)
			else
				select @LeadingZeroString = @LeadingZeroString + cast(@LeadingZeroCount as varchar)

			--==========================================================================================
			-- Add the current number's sort code, along with the current number, to the returned sort value
			--==========================================================================================
			if (@DigitCount < 10)
				select @SortValue = @SortValue + '00' + cast(@DigitCount as varchar) + @CurrentNumber
			else if (@DigitCount < 100)
				select @SortValue = @SortValue + '0' + cast(@DigitCount as varchar) + @CurrentNumber
			else
				select @SortValue = @SortValue + cast(@DigitCount as varchar) + @CurrentNumber

			--==========================================================================================
			-- Reset for the next iteration
			--==========================================================================================
			select @DigitCount = 0
			select @CurrentNumber = ''
			select @LeadingZeroCount = 0
		end -- if (isnumeric(@Leftmost) = 1)

		--==========================================================================================
		-- The character we are currently working with is not a number, just tag it onto our return value
		--    Ignoring whitespace
		--==========================================================================================
		if (@Leftmost != ' ')
			select @SortValue = @SortValue + @Leftmost

		--==========================================================================================
		-- Trim off the character we just checked and continue the main loop
		--==========================================================================================
		select @WorkingItem = substring(@WorkingItem, 2, 200)

	end -- while (len(@WorkingItem) > 0)

	if (@SortNumericsFirst = 0 and @FirstIsCharacter = 1)
		select @SortValue = '-999999999' + @SortValue

	--==========================================================================================
	-- Finally, tag on the leading zero value and return our sort value
	--==========================================================================================
	select @SortValue = @SortValue +  ' ' + @LeadingZeroString

	return @SortValue
END

Open in new window

0
 
Darrell PorterEnterprise Business Process ArchitectCommented:
If you don't want to define a function within SQL, you may want to look at PATINDEX with the '%[0-9]%' construct against 'Distinct'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
I am confused by the expected results shown in the question. It isn't just as simple as ignoring alphabetic characters. Also not that "distinct" is a keyword and not a column name (the column name is missing from the provided query).

Is this close?
| from_db | row_no |
|---------|--------|
|       1 |      1 |
|   10(a) |      2 |
|   11(a) |      3 |
|   12(a) |      4 |
|   13(a) |      5 |
|   14(a) |      6 |
|   15(a) |      7 |
|   15(b) |      8 |
|   15(c) |      9 |
|   16(a) |     10 |
|   16(b) |     11 |
|   16(c) |     12 |
|   17(a) |     13 |
|   17(b) |     14 |
|   17(c) |     15 |
|   18(a) |     16 |
|   18(b) |     17 |
|   18(c) |     18 |
|   19(a) |     19 |
|   19(b) |     20 |
|       2 |     21 |
|   20(a) |     22 |
|   20(b) |     23 |
|      21 |     24 |
|      22 |     25 |
|      23 |     26 |
|      24 |     27 |
|      25 |     28 |
|      26 |     29 |
|      27 |     30 |
|      28 |     31 |
|      29 |     32 |
|       3 |     33 |
|      30 |     34 |
|      31 |     35 |
|      32 |     36 |
|      33 |     37 |
|      34 |     38 |
|   35(a) |     39 |
|   35(b) |     40 |
|   35(c) |     41 |
|   35(d) |     42 |
|   35(e) |     43 |
|   35(f) |     44 |
|   36(a) |     45 |
|   36(b) |     46 |
|   36(c) |     47 |
|   36(d) |     48 |
|   36(e) |     49 |
|   36(f) |     50 |
|   37(a) |     51 |
|   38(a) |     52 |
|      39 |     53 |
|       4 |     54 |
|   40(a) |     55 |
|   41(a) |     56 |
|   42(a) |     57 |
|   42(b) |     58 |
|   42(c) |     59 |
|   43(a) |     60 |
|   43(b) |     61 |
|   43(c) |     62 |
|      44 |     63 |
|       5 |     64 |
|       6 |     65 |
|    7(a) |     66 |
|    7(b) |     67 |
|    8(a) |     68 |
|    8(b) |     69 |
|    9(a) |     70 |
|  (null) |     71 |
|  (null) |     72 |
|  (null) |     73 |
|  (null) |     74 |

Open in new window

that was produced by this query:
select
      [from_db]
     , row_number() over(order by coalesce([from_db],'999999999')) as row_no
from table1
order by coalesce([from_db],'999999999')

Open in new window

from this sample data:
CREATE TABLE Table1
    ([From_DB] varchar(5), [Need_this] varchar(5))
;
    
INSERT INTO Table1
    ([From_DB], [Need_this])
VALUES
    ('1', '1'),
    ('10(a)', '2'),
    ('11(a)', '3'),
    ('12(a)', '4'),
    ('13(a)', '5'),
    ('14(a)', '6'),
    ('15(a)', '7'),
    ('15(b)', '7(a)'),
    ('15(c)', '7(b)'),
    ('16(a)', '8'),
    ('16(b)', '8(a)'),
    ('16(c)', '8(b)'),
    ('17(a)', '9'),
    ('17(b)', '9(a)'),
    ('17(c)', '10'),
    ('18(a)', '10(a)'),
    ('18(b)', '11(a)'),
    ('18(c)', '12(a)'),
    ('19(a)', '13(a)'),
    ('19(b)', '14(a)'),
    ('2', '15(a)'),
    ('20(a)', '15(b)'),
    ('20(b)', '15(c)'),
    ('21', '16(a)'),
    ('22', '16(b)'),
    ('23', '16(c)'),
    ('24', '17(a)'),
    ('25', '17(b)'),
    ('26', '17(c)'),
    ('27', '18(a)'),
    ('28', '18(b)'),
    ('29', '18(c)'),
    ('3', '19(a)'),
    ('30', '19(b)'),
    ('31', '20(a)'),
    ('32', '20(b)'),
    ('33', '21'),
    ('34', '22'),
    ('35(a)', '23'),
    ('35(b)', '24'),
    ('35(c)', '25'),
    ('35(d)', '26'),
    ('35(e)', '27'),
    ('35(f)', '28'),
    ('36(a)', '29'),
    ('36(b)', '30'),
    ('36(c)', '31'),
    ('36(d)', '32'),
    ('36(e)', '33'),
    ('36(f)', '34'),
    ('37(a)', '35(a)'),
    ('38(a)', '35(b)'),
    ('39', '35(c)'),
    ('4', '35(d)'),
    ('40(a)', '35(e)'),
    ('41(a)', '35(f)'),
    ('42(a)', '36(a)'),
    ('42(b)', '36(b)'),
    ('42(c)', '36(c)'),
    ('43(a)', '36(d)'),
    ('43(b)', '36(e)'),
    ('43(c)', '36(f)'),
    ('44', '37(a)'),
    ('5', '38(a)'),
    ('6', '39'),
    ('7(a)', '40(a)'),
    ('7(b)', '41(a)'),
    ('8(a)', '42(a)'),
    ('8(b)', '42(b)'),
    ('9(a)', '42(c)'),
    (NULL, '43(a)'),
    (NULL, '43(b)'),
    (NULL, '43(c)'),
    (NULL, '44')
;

Open in new window

see: http://sqlfiddle.com/#!18/48eb2/1
0
 
Mario RichardsonHealthcare AnalystAuthor Commented:
Good Day PortletPaul,

Sorry for the confusion.

So the results I am trying to get is the following.  In the Sql I am doing a distinct only do to this table has several respondents and for now I am only concerned with getting the order of the one column to be sequenced in the fashion below:


Ex.

If I do an order by Column for this one column regardless of Distinct I get the following:

1
1(a)
1(b)
10
11
12(a)
13(a)
13(b)
2
20
21(a)
22(a)
22(b)
3
3(a)
4
41(a)

what I am looking to have is the following:

1
1(a)
1(b)
2
3
3(a)
4
10
11
12(a)
13(a)
13(b)
20
21(a)
22(a)
22(b)
41(a)

I hope that helps.
0
 
Scott PletcherSenior DBACommented:
SELECT col_name
FROM dbo.table_name
CROSS APPLY (
    SELECT        
        CAST(LEFT(col_name, PATINDEX('%[^0-9]%', col_name + '.') - 1) AS int) AS sort_col1, /*all leading numeric (only) chars*/
        SUBSTRING(col_name, PATINDEX('%[^0-9]%', col_name + '.'), 100) AS sort_col2 /*all chars from first non-numeric char on*/
) AS alias1
ORDER BY sort_col1, sort_col2
0
 
awking00Commented:
select from_db from yourtable
order by case when charindex('(',from_db) = 0 then cast(from_db as int)
              else cast(substring(from_db,1,charindex('(',from_db) - 1) as int)  end,
         case when charindex('(',from_db) != 0 then substring(from_db,charindex('(',from_db), len(from_db)) else null  end;
0
 
Mark WillsTopic AdvisorCommented:
Your "NEED_THIS" column seems to go off the rails at 17(c) and that throws out 18(a) etc....

If 17(b) should be 9(a) then why isnt 17(c) becoming 9(b) - you show it as 10 - Maybe 17(c) should be 18 ?

Assuming (hopefully) that is a typo because it does get pear shaped from that point on.
;With CTE as
(  select SRC.[from_db], TRF.[number]
          ,row_number() over (order by TRF.[number],src.[from_db]) as rn
          ,dense_rank() over (order by TRF.[number]) as dr
          ,row_number() over (partition by TRF.[number] order by src.[from_db]) as rr
   from Your_Table SRC
   cross apply (select left([from_db],charindex('(',[from_db]+'(')-1) as [number]) TRF
)  select [from_db], case when rr = 1 then cast(dr as varchar) else cast(dr as varchar) + '(' + char( 95 + rr ) + ')' end as [Need_This]
   from CTE
   order by [from_db],[number]

Open in new window

0
 
awking00Commented:
Have you tried my query? I don't have access to  SQL Server, so I ran an equivalent (I hope) query in Oracle.  I wasn't able to test it so it may need some tweaking of SQL Server syntax.
0
 
Mario RichardsonHealthcare AnalystAuthor Commented:
Thanks all for this help.  I was able to take a bit from each to derive this.  My apologies if I was not complete in my explanation.  Here is a better example.

From_DB      Need_This
1      1
2      2
3      3
4      4
5      5
6      6
7(a)              7
7(b)              8
8(a)              9
8(b)            10
9(a)             11
10(a)      12
11(a)      13
12(a)      14
13(a)      15
14(a)      16
15(a)      17
15(b)      18
15(c)      19
16(a)      20
16(b)      21
16(c)      22
17(a)      23
17(b)      24
17(c)      25
18(a)      26
18(b)      27
18(c)      28
19(a)      29
19(b)      30
20(a)      31
20(b)      32
21              33
22              34
23              35
24              36
25              37
26              38
27              39
28              40
29              41
30              42
31              43
32              44
33              45
34              46
35(a)      47
35(b)      48
35(c)      49
35(d)      50
35(e)      51
35(f)      52
36(a)      53
36(b)      54
36(c)      55
36(d)      56
36(e)      57
36(f)      58
37(a)      59
38(a)      60
39              61
40(a)      62
41(a)      63
42(a)      64
42(b)      65
42(c)      66
43(a)      67
43(b)      68
43(c)      69
44              70


Here is the query I created based on what Portlet Paul showed.


DROP TABLE #qnum

SELECT DISTINCT Col1,
                From_DB,
                Need_This
INTO   #qnum
FROM   EDWStage.Zarca_SRC.Questionset
WHERE  EtlRunID <> '-1'
ORDER  BY QuestionSet_id

SELECT Col1,
       From_DB,
       Row_number()
         OVER(
           PARTITION BY Col1
           ORDER BY From_DB) 'Need_This'
FROM   #qnum
ORDER  BY Col1,
         Row_number()
         OVER(
           PARTITION BY Col1
           ORDER BY Need_This)

Thanks for everyone's help.

Kind regards,

MJR
0
 
Mark WillsTopic AdvisorCommented:
@mjr,

That is a very different result from your original [Need_This]

Change of mind ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.