ORDER BY on varchar Column with Numbers I need help putting together a query so I can take action when the following occurs:
&quot;Trigger an email when regression result fails three or more label versions consecutively for a given block&quot;
EDIT- had to remove img
\The example above is a good case. But if I have a block with
&quot;IP_BLOCK_X.info#9&quot;
&quot;IP_BLOCK_X.info#8&quot;
&quot;IP_BLOCK_X.info#11&quot;
The result of the query will be ordered incorrectly (9, then 8, then 1 is evaluated, not '11').
EDIT: Here is the table with column names and data types for reference. Table name is 'dbo.regression_full'

The BIG Picture
In my mind, what I need to do for a given block:

Get the last 3 unique labels from Regression_name (top/distinct?)

Out of those, get Block, Timestamp, Regression_name, and Result



ANSWER:
I got it!

Query

Results


Thanks for all the help, this website is amazing! I'll definitely contribute more in areas I know. This was my first post, thanks!
If your Label has fixed pattern, i.e. IP_BLOCK_X.info#NNN, where NNN is an integer.
You can easily do this way

use tempdb 
drop table #t;
create table #t (Block varchar(100), Label varchar(100));
go
insert into #t (Block, Label) values ('Block_A','IP_BLOCK_X.info#9'), ('Block_A','IP_BLOCK_X.info#8'), ('Block_A','IP_BLOCK_X.info#11')
go

select * from #t 
order by cast(substring(Label, len('IP_BLOCK_X_info#')+1, 100) as int)


The result is

Block   Label
Block_A IP_BLOCK_X.info#8
Block_A IP_BLOCK_X.info#9
Block_A IP_BLOCK_X.info#11


Edit:

After the question is updated, as promised, here is my best guess for the answer.

use tempdb 
--drop table #t;
create table #t (Regression_name varchar(100), [TimeStamp] datetime, Block varchar(100), Label varchar(100));
go
insert into #t (Regression_name, [TimeStamp], [Block], Label) 
values ('Regression_1', '2016-11-01','Block_A','IP_BLOCK_X.info#8')
, ('Regression_1', '2016-11-02','Block_A','IP_BLOCK_X.info#9')
, ('Regression_1', '2016-11-03','Block_A','IP_BLOCK_X.info#11')
, ('Regression_1', '2016-11-04','Block_A','IP_BLOCK_X.info#21')
, ('Regression_2', '2016-11-04','Block_B','IP_BLOCK_X.info#8')
, ('Regression_2', '2016-11-05','Block_B','IP_BLOCK_X.info#9')
, ('Regression_2', '2016-11-06','Block_B','IP_BLOCK_X.info#11')
, ('Regression_2', '2016-11-07','Block_B','IP_BLOCK_X.info#21')
go

-- find the latest (by [TimeStamp] top 3 Regression_name for each [Block] and order by [Label]'s last numeric number
; with c as (
select distinct Regression_name, [Block], [TimeStamp]
, rnk = ROW_NUMBER() over (partition by [Block], Regression_name order by [TimeStamp] desc)
from #t) --select * from c
select t.* 
from #t t
inner join c 
on t.Regression_name = c.Regression_name
and t.Block = c.Block
and t.TimeStamp = c.TimeStamp
where c.rnk &lt;=3
order by t.Regression_name, t.Block, t.[TimeStamp],  cast(substring(t.Label, len('IP_BLOCK_X_info#')+1, 100) as int)