Select a row in Windowed select based on set time difference

1

Using this table and data:

CREATE TABLE [dbo].[MyTable](
[session] [bigint] NULL,
[an] [varchar](10) NULL,
[skformat] [int] NULL,
[skmean] [int] NULL,
[mediatype] [varchar](10) NULL,
[rectime] [datetime] NULL,
[prod] [varchar](10) NULL
)

GO

INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'123abc', 2, 3, N'pdf', CAST(N'2017-09-27 
11:51:08.210' AS DateTime), N'abc')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'123abc', 2, 3, N'pdf', CAST(N'2017-09-27 
11:56:08.210' AS DateTime), N'def')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'456hlm', 4, 5, N'hlm', CAST(N'2017-09-27 
11:51:09.210' AS DateTime), N'ghi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'456hlm', 4, 5, N'hlm', CAST(N'2017-09-27 
11:51:10.210' AS DateTime), N'xyz')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'1a2b3c', 2, 2, N'pdf', CAST(N'2017-09-27 
11:51:08.210' AS DateTime), N'fbi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:44.050' AS DateTime), N'abc')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:40.050' AS DateTime), N'def')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'5x5y5z', 4, 6, N'hlm', CAST(N'2017-09-27 
11:54:45.050' AS DateTime), N'ghi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'5x5y5z', 4, 6, N'hlm', CAST(N'2017-09-27 
11:54:45.050' AS DateTime), N'xyz')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xxx', 1, 2, N'pdf', CAST(N'2017-09-27 
11:54:44.050' AS DateTime), N'fbi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:39.050' AS DateTime), NULL)

I created this windowed query:

select session,an,skformat,skmean,mediatype,rectime,
row_number() over 
 (partition by session,an,skformat,skmean,mediatype
 order by rectime asc) as row
 from MyTable
 group by session,an,skformat,skmean,mediatype,rectime
 order by session, an;

Good so far...

But what I'd like to do is just select the first row of each grouping (when there is more than one row) IF the time difference (rectime field) is less than 10 seconds when mediatype=pdf and is less than 30 seconds when mediatype=hlm

Stuck here. Advice appreciated.

This this the result I'm looking for:

session an         skformat    skmean  mediatype   rectime          row

123      123abc  2                3            pdf               11:51:08 AM 1

123      123abc  2                3            pdf               11:56:08 AM 2

123      1a2b3c  2                2            pdf               11:51:08 AM 1

123       456hlm  4               5            hlm              11:51:09 AM 1

555       555xxx  1               2            pdf               11:54:44 AM 1

555       555xyz  1               1            pdf               11:54:39 AM 1

555       5x5y5z  4               6            hlm              11:54:45 AM 1

Thanks!

2

I think this might work for you:

select *
  from (select top(1) with ties
               session
              ,an
              ,skformat
              ,skmean
              ,mediatype
              ,rectime
          from dbo.mytable
         order by row_number() over(partition by session
                                                ,an
                                                ,skformat
                                                ,skmean
                                                ,mediatype
                                        order by rectime
                                   )
       ) as a
 order by session
         ,an
;

Or this:

  from (select top(1) with ties
               session
              ,an
              ,skformat
              ,skmean
              ,mediatype
              ,rectime
              ,row_number() over(partition by session
                                             ,an
                                             ,skformat
                                             ,skmean
                                             ,mediatype
                                     order by rectime
                                )
               as rn
          from dbo.mytable
       ) as a
 where rn=1
 order by session
         ,an
3

Thanks, but unfortunately that does not work.