1

I need help putting together a query so I can take action when the following occurs:

"Trigger an email when regression result fails three or more label versions consecutively for a given block"

EDIT- had to remove img

\The example above is a good case. But if I have a block with

"IP_BLOCK_X.info#9"

"IP_BLOCK_X.info#8"

"IP_BLOCK_X.info#11"

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:

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

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



ANSWER:

I got it!

Query Solution Query

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

  • 1
    You need to convert the number part into a number, so construct 2 (or more) columns from the string, and order by each of them – James Z Dec 21 '16 at 17:43
  • This is not natively supported in SQL Server, but that doesn't mean that it can't someday be natively supported. Please support my Connect Suggestion, Support "natural sorting" / DIGITSASNUMBERS as a Collation option, so that maybe someday this can be built-in functionality. – Solomon Rutzky Dec 21 '16 at 18:43
  • Please don't put the answer in the question. If your answer has anything additional or different than the other answers, add it as a new answer. – ypercubeᵀᴹ Dec 21 '16 at 22:45
2

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 <=3
order by t.Regression_name, t.Block, t.[TimeStamp],  cast(substring(t.Label, len('IP_BLOCK_X_info#')+1, 100) as int)
  • That's cool. Here's more info: First I need to get the last 3 Regression_name then query. Can you include TOP and DISTINCT? – user113411 Dec 21 '16 at 18:22
  • I mean, query to order distinct Regression_name for a given block, then take top 3 Regression_name with Result in the table. – user113411 Dec 21 '16 at 18:24
  • Yes, you can. But if you could provide more detailed sample data, I can modify my answer to meet your requirements. For example, I do not see you have Regress_name column in your sample data. – jyao Dec 21 '16 at 18:26
  • okay, i updated the post with table name and column names. – user113411 Dec 21 '16 at 18:41
  • I also updated my post with "Big Picture" info to describe what I'm ultimately trying to achieve. Thanks for the help so far! – user113411 Dec 21 '16 at 19:02
1

You can use charindex and sub string to build a column to order by

So you could do something like this:

SELECT info
FROM (
    SELECT info, 
    CAST(SUBSTRING(info,CHARINDEX('#',info,0)+1,(LEN(info)-CHARINDEX('#',info,0))) AS INT) [OrderBy_Column]
    FROM (
        SELECT 'IP_BLOCK_X.info#8' AS info
        UNION ALL 
        SELECT 'IP_BLOCK_X.info#9' AS info
        UNION ALL 
        SELECT 'IP_BLOCK_X.info#11' AS info
        UNION ALL 
        SELECT 'IP_BLOCK_X.info#934' AS info
    ) AS info
) AS data
ORDER BY [OrderBy_Column]

Which Would output:

IP_BLOCK_X.info#8
IP_BLOCK_X.info#9
IP_BLOCK_X.info#11
IP_BLOCK_X.info#934
  • Nice! Can you tell me how to incorporate with my own table? – user113411 Dec 21 '16 at 18:35
  • Just add your other fields to each select level and replace the inner union select with a select from your table. – JasonBluefire Dec 21 '16 at 18:36
  • You could also use right(Label,charindex('#',reverse(Label))-1) instead of substring in this instance. – SqlZim Dec 21 '16 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.